Menu
I'm trying to write a query like this in PostgreSQL 9.5.2:
INSERT INTO a (id, x) SELECT id, x FROM b ON CONFLICT (id) DO UPDATE SET x = b.x WHERE b.y < 100 but I get ERROR: missing FROM-clause entry for table "b". I must be missing something basic, but how do I refer to the row being inserted in the UPDATE clause? Or is there some other way?
268
In relational databases, the term upsert is referred to as merge. The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).
PostgreSQL lets you either add or modify a record within a table depending on whether the record already exists. This is commonly known as an "upsert" operation (a portmanteau of "insert" and "update").
PostgreSQL INSERT Multiple Rows First, specify the name of the table that you want to insert data after the INSERT INTO keywords. Second, list the required columns or all columns of the table in parentheses that follow the table name. Third, supply a comma-separated list of rows after the VALUES keyword.
It is very easy to update multiple columns in PostgreSQL. Here is the syntax to update multiple columns in PostgreSQL. UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE condition];
The conflicting values are available through the excluded alias:
INSERT INTO a (id, x) SELECT id, x FROM b ON CONFLICT (id) DO UPDATE SET x = excluded.x; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
