sql upsert statement- postgresql

Question

Table

id | name | value
0  | apis | apple

I want to append ,orange to this value, value should look something like apple,orange

I am trying to execute this query however let's suppose the original value is apple, so I am expecting the final value to be apple,orange but I am getting orange,orange. Seems like the EXCLUDED.value is reading from INSERT STATEMENT, how do I get the original value appended by comma and orange (apple,orange)

INSERT INTO my_table(id, name, value)
       VALUES(0, 'apis', 'dummyapi')
ON CONFLICT (id,name)
DO UPDATE set value = EXCLUDED.value || ',dummyapi'
    where my_table.name = 'apis'
      AND my_table.id=0
      AND my_table.value not ilike '%dummyapi%';

Jim Jones · Accepted Answer · 2021-08-03 13:43:28Z

2

In your query you're concatenaing the EXCLUDED variable with a string and therefore overwriting the contents of value, check this out:

INSERT INTO my_table(id, name, value) VALUES(0, 'apis', 'orange')
ON CONFLICT (id,name)
DO UPDATE set value = my_table.value || ',' || EXCLUDED.VALUE;

Demo: db<>fiddle

answered Aug 3, 2021 at 13:43

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql upsert statement- postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related