RE: Issues with upserts
| От | André Hänsel |
|---|---|
| Тема | RE: Issues with upserts |
| Дата | |
| Msg-id | 034701d896c8$fe2edfa0$fa8c9ee0$@webkr.de обсуждение исходный текст |
| Ответ на | Re: Issues with upserts (Jeremy Smith <jeremy@musicsmith.net>) |
| Ответы |
Re: Issues with upserts
Re: Issues with upserts Re: Issues with upserts |
| Список | pgsql-general |
Jeremy Smith wrote:
It sounds like you aren't adding a WHERE clause to prevent the duplicate rows from being updated. It would help if you could share your query, but in general this could look like this:
INSERT INTO my_table (col1, col2)
SELECT col1, col2 FROM other_table
ON CONFLICT (col1, col2) DO UPDATE SET (col1, col2) = (EXCLUDED.col1, EXCLUDED.col2)
WHERE (my_table.col1, my_table.col2) IS DISTINCT FROM (EXCLUDED.col1, EXCLUDED.col2)
Here’s an example:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=b48d062d2eedbab14157359694b16081
CREATE TABLE t (
id serial PRIMARY KEY,
name text NOT NULL UNIQUE,
address text NOT NULL
);
INSERT INTO t(name, address)
VALUES ('foo', 'Baker street'),('bar', 'Miller street')
ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address
WHERE (t.name, t.address) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.address);
INSERT INTO t(name, address)
VALUES ('foo', 'Baker street'),('bar', 'Miller street')
ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address
WHERE (t.name, t.address) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.address);
INSERT INTO t(name, address)
VALUES ('foo', 'Baker street'),('bar', 'Miller street')
ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address
WHERE (t.name, t.address) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.address);
INSERT INTO t(name, address)
VALUES ('foo', 'Baker street'),('bar', 'Miller street')
ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address
WHERE (t.name, t.address) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.address);
SELECT last_value FROM t_id_seq;
This will yield “8”, showing that new sequence numbers have been generated for each attempt.
В списке pgsql-general по дате отправления: