How to Update All Columns with Insert ... on Conflict ...

How to update all columns with INSERT ... ON CONFLICT ...?

The UPDATE syntax requires to explicitly name target columns.
Possible reasons to avoid that:

  • You have many columns and just want to shorten the syntax.
  • You do not know column names except for the unique column(s).

"All columns" has to mean "all columns of the target table" (or at least "leading columns of the table") in matching order and matching data type. Else you'd have to provide a list of target column names anyway.

Test table:

CREATE TABLE tbl (
id int PRIMARY KEY
, text text
, extra text
);

INSERT INTO tbl VALUES
(1, 'foo')
, (2, 'bar')
;

1. DELETE & INSERT in single query instead

Without knowing any column names except id.

Only works for "all columns of the target table". While the syntax even works for a leading subset, excess columns in the target table would be reset to their respective column default (default NULL) with DELETE and INSERT.

UPSERT (INSERT ... ON CONFLICT ...) is needed to avoid concurrency / locking issues under concurrent write load, and only because there is no general way to lock not-yet-existing rows in Postgres (value locking).

Your special requirement only affects the UPDATE part. Possible complications do not apply where existing rows are affected. Those are locked properly. Simplifying some more, you can reduce your case to DELETE and INSERT:

WITH data(id) AS (              -- Only 1st column gets explicit name
VALUES
(1, 'foo_upd', 'a') -- changed
, (2, 'bar', 'b') -- unchanged
, (3, 'baz', 'c') -- new
)
, del AS (
DELETE FROM tbl AS t
USING data d
WHERE t.id = d.id
-- AND t <> d -- optional, to avoid empty updates
) -- only works for complete rows
INSERT INTO tbl AS t
TABLE data -- short for: SELECT * FROM data
ON CONFLICT (id) DO NOTHING
RETURNING t.id;

In the Postgres MVCC model, an UPDATE is largely the same as DELETE and INSERT - except for some corner cases with concurrency, triggers, HOT updates, and big column values stored out of line, "TOASTed" values. Since you want to replace all rows anyway, just remove conflicting rows before the INSERT. Deleted rows remain locked until the transaction is committed. The INSERT might only find conflicting rows for previously non-existing key values if a concurrent transaction happens to insert them concurrently (after the DELETE, but before the INSERT).

You would lose additional column values for affected rows in this special case. No exception raised. But if competing queries have equal priority, that's hardly a problem: the other query won for some rows. Also, if the other query is a similar UPSERT, its alternative is to wait for this transaction to commit and then updates right away. "Winning" could be a Pyrrhic victory.

About "empty updates":

  • How do I (or can I) SELECT DISTINCT on multiple columns?

No, my query must win!

OK, you asked for it:

WITH data(id) AS (                   -- Only 1st column gets explicit name
VALUES -- rest gets default names "column2", etc.
(1, 'foo_upd', NULL) -- changed
, (2, 'bar', NULL) -- unchanged
, (3, 'baz', NULL) -- new
, (4, 'baz', NULL) -- new
)
, ups AS (
INSERT INTO tbl AS t
TABLE data -- short for: SELECT * FROM data
ON CONFLICT (id) DO UPDATE
SET id = t.id
WHERE false -- never executed, but locks the row!
RETURNING t.id
)
, del AS (
DELETE FROM tbl AS t
USING data d
LEFT JOIN ups u USING (id)
WHERE u.id IS NULL -- not inserted!
AND t.id = d.id
-- AND t <> d -- avoid empty updates - only for full rows
RETURNING t.id
)
, ins AS (
INSERT INTO tbl AS t
SELECT *
FROM data
JOIN del USING (id) -- conflict impossible!
RETURNING id
)
SELECT ARRAY(TABLE ups) AS inserted -- with UPSERT
, ARRAY(TABLE ins) AS updated; -- with DELETE & INSERT

How?

  • The 1st CTE data just provides data. Could be a table instead.
  • The 2nd CTE ups: UPSERT. Rows with conflicting id are not changed, but also locked.
  • The 3rd CTE del deletes conflicting rows. They remain locked.
  • The 4th CTE ins inserts whole rows. Only allowed for the same transaction
  • The final SELECT is optional, to show what happened.

To check for empty updates test (before and after) with:

SELECT ctid, * FROM tbl; -- did the ctid change?

The (commented out) check for any changes in the row AND t <> d works even with NULL values because we are comparing two typed row values according to the manual:

two NULL field values are considered equal, and a NULL is considered larger than a non-NULL

But all columns must support = / <> operators for the row comparison to work. See:

  • How to query a json column for empty objects?

2. Dynamic SQL

This works for a subset of leading columns too, preserving existing values.

The trick is to let Postgres build the query string with column names from the system catalogs dynamically, and then execute it.

See related answers for code:

  • Update multiple columns in a trigger function in plpgsql

  • Bulk update of all columns

  • SQL update fields of one table from fields of another one

Use extra columns in INSERT values list in ON CONFLICT UPDATE

In the SET part, you can only reference columns of the target table and the corresponding "values" through the excluded record. Neither of them has a column named z

The only way I can think of, is to put the values into a CTE and access the column z through a sub-query:

with data (x,y,z) as (
VALUES
(123, 4, 6),
(345, 2, 3)
)
INSERT INTO test (a,b)
select d1.x, d1.y
from data d1
ON CONFLICT (a) DO UPDATE
SET b = (select d2.z from data d2 where d2.x = excluded.a);

The above assumes that a is the primary (or unique) key of the table.

Online example

Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?

Unfortunately not.

You can get half-way there by not having to repeat the value:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);

But you still have to list the columns.

How do I insert multiple records with an ON CONFLICT UPDATE clause in Postgres?

Use the excluded record:

INSERT INTO orders(
order_id,
payment_status
)
VALUES ('101','Paid'), ('102', 'Unpaid')
ON CONFLICT (order_id) DO UPDATE
SET payment_status = EXCLUDED.payment_status;

insert/update data from one column to another with on conflict update, only those that have different values for specific fields?

The problem is in the where clause. You need to qualify the columns so there are unambiguous:

INSERT INTO products(id,price,quantity,lastupdate)
SELECT id,price,quantity,lastupdate FROM tmp_products
ON CONFLICT (id) DO UPDATE SET
price = EXCLUDED.price,
quantity = EXCLUDED.quantity,
lastupdate = EXCLUDED.lastupdate
WHERE products.price <> EXCLUDED.price OR products.quantity <> EXCLUDED.quantity;

Note that you also need to remove the parentheses around the SELECT, otherwise your query is not valid Postgres SQL, and would raise error INSERT has more target columns than expressions.

Can PostgreSQL's update the other row on INSERT conflict?

As proposed by @Adrian, you can do it with a trigger :

CREATE OR REPLACE FUNCTION before_insert ()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
UPDATE demo
SET current = false
WHERE a = NEW.a ;
RETURN NEW ;
END ;
$$ ;

CREATE TRIGGER before_insert BEFORE INSERT ON demo
FOR EACH ROW EXECUTE FUNCTION before_insert () ;

see test result in dbfiddle

PS : the constraint one_per will prevent from having several former rows for the same a value and with current = false



Related Topics



Leave a reply



Submit