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

[eribbeck]

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.

[hchanana]

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.

Thanks,

Himanshu

[eribbeck]

@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.

[hchanana]

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]

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 · Accepted solution]

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.