CODEWITHSUNDEEP
postgresqlupsert
Salim Fadhley
Salim Fadhley

Reputation: 8195

Upserting into one Postgres table from another table?

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?

Upvotes: 0

Views: 514

Answers (1)

Jeremy
Jeremy

Reputation: 6723

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.

Upvotes: 1

Related Questions