How to update a column with INSERT ... ON CONFLICT only if the old value is NULL-CodePudding

INSERT INTO main_parse_user ("user_id","group_id", "username", "bio", "first_name") 
VALUES ($1,$2,$3,$4,$5) 
ON CONFLICT (user_id) 
DO UPDATE 
SET ("group_id", "username", "bio", "first_name") = (EXCLUDED.group_id, EXCLUDED.username, EXCLUDED.bio,EXCLUDED.first_name)

Here is my code for adding and editing data to db at the same time

Now it works like this:

user_id|group_id|@username|bio|first_name

And if the database had a previously user_id, then it edits and adds data from the new request to the remaining fields

But I need to do it a little differently.

Let's say I have data in the database

12345|-10015488|@abcd|None|Dev

And I added a new line with exactly the same ID but with a change in bio

12345|-10015488|@abcd|Developer|Dev

And when I don’t have bio (None) on the old database, I need to add a new value to the database, and if there was information in bio initially, then the algorithm simply skips

CodePudding user response：

You can use coalesce:

... DO UPDATE
   SET (bio, first_name) =
       (
           coalesce(main_parse_user.bio, EXCLUDED.bio),
           EXCLUDED.first_name
       )

It is necessary to qualify the original column value with the table name to disambiguate it from the new value

CodePudding user response：

INSERT INTO main_parse_user ("user_id","bio","group_id", "username", "first_name") 
    VALUES (%s,%s,%s,%s,%s) 
    ON CONFLICT (user_id) 
    DO UPDATE 
    SET (bio, group_id, username, first_name) = 
    (
        coalesce(main_parse_user.bio, main_parse_user.bio),
        EXCLUDED.group_id, EXCLUDED.username, EXCLUDED.first_name
    )
date = (859726, 'BIO', 0, 'username', 'name')
cur_gre.execute(test, date)
gre.commit()

Why is it not working for me