CODEWITHSUNDEEP
postgresql
TLR
TLR

Reputation: 587

Update new value on conflict

I'd like to update a row with an insert statement. Indeed, I can insert rows but if the unique attribute already exists, I'd like to update the content.

So I have these tables

CREATE TABLE dtest (
  "id" text,
  followers_count int4,
  someuniq int4
);
CREATE UNIQUE INDEX dtest_idx ON dtest USING btree (someuniq);

CREATE TABLE temp_data (
  tid text,
  tfo int4,
  tuniq int4);

Let's consider that I have a temp tab, and I insert/update data from this table

INSERT INTO temp_data VALUES ('id1',4,1);
INSERT INTO temp_data VALUES ('id2',0,2);
INSERT INTO temp_data VALUES ('id3',40,3);


INSERT INTO dtest("id","followers_count","someuniq")
SELECT  t.tid, t.tfo, t.tuniq
FROM    temp_data t
ON CONFLICT DO NOTHING;

Instead of doing an insert and then an update, I'd like to know if it's possible to update the values with something like this

INSERT INTO dtest("id","followers_count","someuniq")
SELECT  tid, tfo, tuniq
FROM    temp_data 
ON CONFLICT ("someuniq")
DO UPDATE SET followers_count = 
        (SELECT tfo FROM temp_data where tid = EXCLUDED.tid)
    WHERE EXCLUDED.id = tid;

Which means, "Update some fields if the row already exists", what am I doing wrong?

Upvotes: 1

Views: 460

Answers (1)

user330315
user330315

Reputation:

There is no need to use a sub-select. The excluded row will contain all target columns, including the followers_count:

INSERT INTO dtest (id, followers_count, someuniq)
SELECT  tid, tfo, tuniq
FROM temp_data 
ON CONFLICT (someuniq)
DO UPDATE 
    SET followers_count = excluded.followers_count;

Upvotes: 1

Related Questions