Postgres Upsert with Select

Question

I'm trying to upsert a record with a select:

INSERT INTO global_search (entity_id, entity_type, search_content)
SELECT id, 'user', "username"
FROM "user"
WHERE "id" = 1
ON CONFLICT (entity_id) DO UPDATE
SET search_content = excluded.username

But I'm getting the following error:

ERROR:  column excluded.username does not exist
LINE 8: SET search_content = excluded.username

This seems like it should work per this question. If I remove the ON CONFLICT bit it works fine. Normal upserts with values lists work fine as well.

I'm running postgresql 13.4.

Are you trying to bulk change rows? Probably it worth checking the new MERGE feature on PostgresSQL >= v15 It would be something like: MERGE INTO s1 USING s2 ON id WHEN MATCHED THEN UPDATE .... WHEN NO MATCHED THEN INSERT... — plmk
– plmk, Commented Oct 14, 2022 at 9:25

Laurenz Albe · Accepted Answer · 2021-10-29 02:12:44Z

1

That would be EXCLUDED.search_content. The column name is that of the target column in the table into which you INSERT.

answered Oct 29, 2021 at 2:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgres Upsert with Select

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related