CODEWITHSUNDEEP
sqlpostgresqlsql-updatesql-insertsql-null
Michael Kročka
Michael Kročka

Reputation: 665

Insert query with update on conflict only when values aren't null

I have a query which goes like this:

INSERT INTO accounts(id, description, followers_count, friends_count, statuses_count)
VALUES(%s, %s, %s, %s, %s)
ON CONFLICT DO UPDATE
SET description=EXCLUDED.description, 
    followers_count=EXCLUDED.followers_count, 
    friends_count=EXCLUDED.friends_count, 
    statuses_count=EXCLUDED.statuses_count;

Now description, followers_count, friends_count, statuses_count can all be NULL. My question is whether this query can be changed to only update when these values aren't NULL.

For example when: description='joey tribbiani' followers_count=45 friends_count=90 statuses_count=15 don't update with NULL values. But when it's the other way around, do the update.

Upvotes: 1

Views: 786

Answers (2)

user330315
user330315

Reputation:

You could use coalesce():

INSERT INTO accounts(id, description, followers_count, friends_count, statuses_count)
VALUES(%s, %s, %s, %s, %s)
ON CONFLICT DO UPDATE
SET description = coalesce(EXCLUDED.description, accounts.description), 
    followers_count = coalesce(EXCLUDED.followers_count, accounts.followers_count),
    friends_count = coalesce(EXCLUDED.friends_count, accounts.friends_count),
    statuses_count = coalesce(EXCLUDED.statuses_count, accounts.statuses_count);

Upvotes: 1

GMB
GMB

Reputation: 222622

You could use COALESCE() in the SET clause:

INSERT INTO accounts(id, description, followers_count, friends_count, statuses_count)
VALUES(%s, %s, %s, %s, %s)
ON CONFLICT DO UPDATE
SET description     = COALESCE(EXCLUDED.description, description)
    followers_count = COALESCE(EXCLUDED.followers_count, followers_count), 
    friends_count   = COALESCE(EXCLUDED.friends_count, friends_count), 
    statuses_count  = COALESCE(EXCLUDED.statuses_count, statuses_count);

When the value given for insert is null, this falls back on the original table value, which turns the assignment to a no-op.

Upvotes: 1

Related Questions