Skip to main content
E
eribbeck1-Visitor
1-Visitor
February 26, 2020
Solved

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

  • February 26, 2020
  • 2 replies
  • 2812 views

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.

    Best answer by eribbeck

    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.

    2 replies

    H
    hchanana16-Pearl
    16-Pearl
    February 26, 2020

    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

    E
    eribbeck1-VisitorAuthor
    1-Visitor
    February 26, 2020

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

    H
    hchanana16-Pearl
    16-Pearl
    February 27, 2020

    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

    S
    slangleyCommunity Manager
    Support
    March 6, 2020

    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

    E
    eribbeck1-VisitorAuthorAnswer
    1-Visitor
    March 9, 2020

    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.

    Terms & ConditionsAccessibility statement