cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

The community will undergo maintenance on October 16th at 10:00 PM PDT and will be unavailable for up to one hour.

How can I update an existing value in a database table to be null?

eribbeck
6-Contributor

How can I update an existing value in a database table to be null?

Hi,

 

I'm trying to update a field in a postgres DB table to be null (really I just want to delete the value that is already there). Currently, the field is a dataType: long.

 

Today, I'm using the out of the box "Update" service from the postgres DB thing. I've tried "updating" the value to be null, undefined, NaN, "" and nothing seems to be working. The update fails each time.

 

I'm aware we could write this in SQL on the postgresDB thing directly, but because we are using a PTC cloud hosted environment we don't have the ability to write SQL functions in production.

 

Any help here is appreciated.

ACCEPTED SOLUTION

Accepted Solutions
eribbeck
6-Contributor
(To:slangley)

None of the above helped me find a solution

 

However, I figured something out on my own. This is far from ideal as it is a risky operation. But in order to address the shortcoming here, I am deleting and re-creating the entire table entry, except the field I was trying to remove.

View solution in original post

5 REPLIES 5

Hi @eribbeck ,

 

Please try passing the column as blank (empty), in the database it will become null.

I tried at my end, below are the screenshots.

 

service.png

 

db.png

 

Thanks,

Himanshu

eribbeck
6-Contributor
(To:hchanana)

@hchanana I should have also specified that the field I'm trying to update to null has a foreign key constraint, as that still doesn't work for me and this field. If I leave a field blank in the update it doesn't update the field.

Hi @eribbeck ,

 

If you are updating a column to null which is a foreign key, then it must be linked to primary key of some other table. And primary key can never be null. Due to which application is not allowing you to update the value to null.

Even if you use database query editor, it will now allow you to update the column with foreign key constraint to null.

 

Hope the explanation clear to you.

 

Thanks,

Himanshu

slangley
23-Emerald II
(To:eribbeck)

Hi @eribbeck.

 

If one of the previous responses allowed you to resolve your issue, please mark the appropriate one as the Accepted Solution for the benefit of others with the same problem.

 

Regards.

 

--Sharon

eribbeck
6-Contributor
(To:slangley)

None of the above helped me find a solution

 

However, I figured something out on my own. This is far from ideal as it is a risky operation. But in order to address the shortcoming here, I am deleting and re-creating the entire table entry, except the field I was trying to remove.

Announcements


Top Tags