CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1postgresql-9.3postgresql-9.2
Shambhavi Rai
Shambhavi Rai

Reputation: 341

sql upsert statement- postgresql

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%';

Upvotes: 1

Views: 101

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

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

Upvotes: 2

Related Questions