Upserting into one Postgres table from another table?

Question

I have two tables with identical structures. All columns are integers and are named "A" , "B" and "key".

I can insert from one table into another with some SQL like this:

INSERT INTO test_table_kewmfsznj
    SELECT * FROM tmp_test_table_kewmfsznj_cnxtbkbq ta

But this doesn't work:

INSERT INTO test_table_kewmfsznj
    SELECT * FROM tmp_test_table_kewmfsznj_cnxtbkbq ta
ON CONFLICT ("key") 
    DO NOTHING

My expectation was that this code would skip any row from "ta" where the key already exists in the table I'm inserting into. Instead I get this error:

ERROR: missing FROM-clause entry for table "ta"
  Position: 152

Here's what I really want to do: When the key already exists in the table I'm inserting into, I want to update certain columns:

INSERT INTO test_table_kewmfsznj
    SELECT * FROM tmp_test_table_kewmfsznj_cnxtbkbq ta
ON CONFLICT ("key") 
    DO UPDATE SET "A" = ta."A", "B" = ta."B"

Unfortunately this gives me (almost) the same error:

ERROR: missing FROM-clause entry for table "ta"
  Position: 134

Can somebody explain what I am doing wrong here?

EDIT0: I tried it without upper case table names. The columns are now called "a", "b" and "key". The data remains unchanged - it's all integers.

INSERT INTO test_table_mrcvnaoia
    SELECT * from tmp_test_table_mrcvnaoia_uuxkaidv ta
ON CONFLICT (key)
    DO UPDATE SET a = ta.a, b = ta.b

... and now I get this error:

SQL Error [42P01]: ERROR: missing FROM-clause entry for table "ta"
  Position: 129

To me, this suggests that there's something wrong with my ON CONFLICT statement, and probably not the first half of the query, but beyond that I'm out of clues. Can anybody help?

Jeremy · Accepted Answer · 2021-03-23 02:18:21Z

2

You almost had it, but you can't reference the table name, you reference EXCLUDED:

INSERT INTO test_table_mrcvnaoia
    SELECT * from tmp_test_table_mrcvnaoia_uuxkaidv
ON CONFLICT (key)
    DO UPDATE SET a = EXCLUDED.a, b = EXCLUDED.b;

Furthermore, to avoid errors in the future, make sure you explicitly specify the column names in the insert and select portions of your statement. They are the same for now, but that might not always be the case.

answered Mar 23, 2021 at 2:18

Jeremy

6,77322 silver badges21 bronze badges

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

4 Comments

Salim Fadhley Over a year ago

does EXCLUDED refer to the record that would be overwritten by the upsert or the incoming record that might potentially overwrite the old record?

Salim Fadhley Over a year ago

What does this mean: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Salim Fadhley Over a year ago

I managed to make this work after defining a unique constraint on "key", but is there a way to make this work without specifying an extra key, just the column name?

Jeremy Over a year ago

There must be a unique constraint. Without a constraint, there would not be a conflict.

Collectives™ on Stack Overflow

Upserting into one Postgres table from another table?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related