ChatGPT解决这个技术问题 Extra ChatGPT

How to use RETURNING with ON CONFLICT in PostgreSQL?

I have the following UPSERT in PostgreSQL 9.5:

INSERT INTO chats ("user", "contact", "name") 
           VALUES ($1, $2, $3), 
                  ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO NOTHING
RETURNING id;

If there are no conflicts it returns something like this:

----------
    | id |
----------
  1 | 50 |
----------
  2 | 51 |
----------

But if there are conflicts it doesn't return any rows:

----------
    | id |
----------

I want to return the new id columns if there are no conflicts or return the existing id columns of the conflicting columns.
Can this be done? If so, how?

Use ON CONFLICT UPDATE so there is a change to the row. Then RETURNING will capture it.
@GordonLinoff What if there's nothing to update?
If there is nothing to update, it means there was no conflict so it just inserts the new values and return their id
You'll find other ways here. I'd love to know the difference between the two in terms of performance though.

E
Erwin Brandstetter

The currently accepted answer seems ok for a single conflict target, few conflicts, small tuples and no triggers. It avoids concurrency issue 1 (see below) with brute force. The simple solution has its appeal, the side effects may be less important.

For all other cases, though, do not update identical rows without need. Even if you see no difference on the surface, there are various side effects:

It might fire triggers that should not be fired.

It write-locks "innocent" rows, possibly incurring costs for concurrent transactions.

It might make the row seem new, though it's old (transaction timestamp).

Most importantly, with PostgreSQL's MVCC model a new row version is written for every UPDATE, no matter whether the row data changed. This incurs a performance penalty for the UPSERT itself, table bloat, index bloat, performance penalty for subsequent operations on the table, VACUUM cost. A minor effect for few duplicates, but massive for mostly dupes.

Plus, sometimes it is not practical or even possible to use ON CONFLICT DO UPDATE. The manual:

For ON CONFLICT DO UPDATE, a conflict_target must be provided.

A single "conflict target" is not possible if multiple indexes / constraints are involved. But here is a related solution for multiple partial indexes:

UPSERT based on UNIQUE constraint with NULL values

Back on the topic, you can achieve (almost) the same without empty updates and side effects. Some of the following solutions also work with ON CONFLICT DO NOTHING (no "conflict target"), to catch all possible conflicts that might arise - which may or may not be desirable.

Without concurrent write load

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- return more columns?
   )
SELECT 'i' AS source                           -- 'i' for 'inserted'
     , id  --, usr, contact                    -- return more columns?
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' for 'selected'
     , c.id  --, usr, contact                  -- return more columns?
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- columns of unique index

The source column is an optional addition to demonstrate how this works. You may actually need it to tell the difference between both cases (another advantage over empty writes).

The final JOIN chats works because newly inserted rows from an attached data-modifying CTE are not yet visible in the underlying table. (All parts of the same SQL statement see the same snapshots of underlying tables.)

Since the VALUES expression is free-standing (not directly attached to an INSERT) Postgres cannot derive data types from the target columns and you may have to add explicit type casts. The manual:

When VALUES is used in INSERT, the values are all automatically coerced to the data type of the corresponding destination column. When it's used in other contexts, it might be necessary to specify the correct data type. If the entries are all quoted literal constants, coercing the first is sufficient to determine the assumed type for all.

The query itself (not counting the side effects) may be a bit more expensive for few dupes, due to the overhead of the CTE and the additional SELECT (which should be cheap since the perfect index is there by definition - a unique constraint is implemented with an index).

May be (much) faster for many duplicates. The effective cost of additional writes depends on many factors.

But there are fewer side effects and hidden costs in any case. It's most probably cheaper overall.

Attached sequences are still advanced, since default values are filled in before testing for conflicts.

About CTEs:

Are SELECT type queries the only type that can be nested?

Deduplicate SELECT statements in relational division

With concurrent write load

Assuming default READ COMMITTED transaction isolation. Related:

Concurrent transactions result in race condition with unique constraint on insert

The best strategy to defend against race conditions depends on exact requirements, the number and size of rows in the table and in the UPSERTs, the number of concurrent transactions, the likelihood of conflicts, available resources and other factors ...

Concurrency issue 1

If a concurrent transaction has written to a row which your transaction now tries to UPSERT, your transaction has to wait for the other one to finish.

If the other transaction ends with ROLLBACK (or any error, i.e. automatic ROLLBACK), your transaction can proceed normally. Minor possible side effect: gaps in sequential numbers. But no missing rows.

If the other transaction ends normally (implicit or explicit COMMIT), your INSERT will detect a conflict (the UNIQUE index / constraint is absolute) and DO NOTHING, hence also not return the row. (Also cannot lock the row as demonstrated in concurrency issue 2 below, since it's not visible.) The SELECT sees the same snapshot from the start of the query and also cannot return the yet invisible row.

Any such rows are missing from the result set (even though they exist in the underlying table)!

This may be ok as is. Especially if you are not returning rows like in the example and are satisfied knowing the row is there. If that's not good enough, there are various ways around it.

You can check the row count of the output and repeat the statement if it does not match the row count of the input. May be good enough for the rare case. The point is to start a new query (can be in the same transaction), which will then see the newly committed rows.

Or check for missing result rows within the same query and overwrite those with the brute force trick demonstrated in Alextoni's answer.

WITH input_rows(usr, contact, name) AS ( ... )  -- see above
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact                   -- we need unique columns for later join
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' for 'inserted'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' for 'selected'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- RARE corner case
   INSERT INTO chats AS c (usr, contact, name)  -- another UPSERT, not just UPDATE
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)     -- columns of unique index
   WHERE  s.usr IS NULL                         -- missing!
   ON     CONFLICT (usr, contact) DO UPDATE     -- we've asked nicely the 1st time ...
   SET    name = c.name                         -- ... this time we overwrite with old value
   -- SET name = EXCLUDED.name                  -- alternatively overwrite with *new* value
   RETURNING 'u'::"char" AS source              -- 'u' for updated
           , id  --, usr, contact               -- return more columns?
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

It's like the query above, but we add one more step with the CTE ups, before we return the complete result set. That last CTE will do nothing most of the time. Only if rows go missing from the returned result, we use brute force.

More overhead, yet. The more conflicts with pre-existing rows, the more likely this will outperform the simple approach.

One side effect: the 2nd UPSERT writes rows out of order, so it re-introduces the possibility of deadlocks (see below) if three or more transactions writing to the same rows overlap. If that's a problem, you need a different solution - like repeating the whole statement as mentioned above.

Concurrency issue 2

If concurrent transactions can write to involved columns of affected rows, and you have to make sure the rows you found are still there at a later stage in the same transaction, you can lock existing rows cheaply in the CTE ins (which would otherwise go unlocked) with:

...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE  -- never executed, but still locks the row
...

And add a locking clause to the SELECT as well, like FOR UPDATE.

This makes competing write operations wait till the end of the transaction, when all locks are released. So be brief.

More details and explanation:

How to include excluded rows in RETURNING from INSERT ... ON CONFLICT

Is SELECT or INSERT in a function prone to race conditions?

Deadlocks?

Defend against deadlocks by inserting rows in consistent order. See:

Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING

Data types and casts

Existing table as template for data types ...

Explicit type casts for the first row of data in the free-standing VALUES expression may be inconvenient. There are ways around it. You can use any existing relation (table, view, ...) as row template. The target table is the obvious choice for the use case. Input data is coerced to appropriate types automatically, like in the VALUES clause of an INSERT:

WITH input_rows AS (
  (SELECT usr, contact, name FROM chats LIMIT 0)  -- only copies column names and types
   UNION ALL
   VALUES
      ('foo1', 'bar1', 'bob1')  -- no type casts here
    , ('foo2', 'bar2', 'bob2')
   )
   ...

This does not work for some data types. See:

Casting NULL type when updating multiple rows

... and names

This also works for all data types.

While inserting into all (leading) columns of the table, you can omit column names. Assuming table chats in the example only consists of the 3 columns used in the UPSERT:

WITH input_rows AS (
   SELECT * FROM (
      VALUES
      ((NULL::chats).*)         -- copies whole row definition
      ('foo1', 'bar1', 'bob1')  -- no type casts needed
    , ('foo2', 'bar2', 'bob2')
      ) sub
   OFFSET 1
   )
   ...

Aside: don't use reserved words like "user" as identifier. That's a loaded footgun. Use legal, lower-case, unquoted identifiers. I replaced it with usr.


You imply this method will not create gaps in the serials, but they are: INSERT ... ON CONFLICT DO NOTHING does increment the serial each time from what I can see
not that it matter that much, but why is it that serials are incremented? and is there no way to avoid this?
Incredible. Works like a charm and easy to understand once you look at it carefully. I still wish ON CONFLICT SELECT... where a thing though :)
@Roshambo: Yep, that would be a lot more elegant. (I added alternatives to explicit type casts while being here.)
Incredible. Postgres' creators seem to be torturing users. Why not just simply make returning clause always return values, regardless of whether there were inserts or not?
R
Ryabchenko Alexander

I had exactly the same problem, and I solved it using 'do update' instead of 'do nothing', even though I had nothing to update. In your case it would be something like this:

INSERT INTO chats ("user", "contact", "name") 
       VALUES ($1, $2, $3), 
              ($2, $1, NULL) 
ON CONFLICT("user", "contact") 
DO UPDATE SET 
    name=EXCLUDED.name 
RETURNING id;

This query will return all the rows, regardless they have just been inserted or they existed before.


One problem with this approach is, that the primary key's sequence number is incremented upon every conflict (bogus update), which basically means that you may end up with huge gaps in the sequence. Any ideas how to avoid that?
@Mischa: so what? Sequences are never guaranteed to be gapless in the first place and gaps don't matter (and if they do, a sequence is the wrong thing to do)
I would not advise to use this in most cases. I added an answer why.
This answer doesn't appear to achieve the DO NOTHING aspect of the original question -- for me it appears to update the non-conflict field (here, "name") for all rows.
As discussed in the very long answer below, using "Do Update" for a field that has not changed is not a "clean" solution and can cause other problems.
Y
Yu Huang
WITH e AS(
    INSERT INTO chats ("user", "contact", "name") 
           VALUES ($1, $2, $3), 
                  ($2, $1, NULL) 
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
)
SELECT * FROM e
UNION
    SELECT id FROM chats WHERE user=$1, contact=$2;

The main purpose of using ON CONFLICT DO NOTHING is to avoid throwing error, but it will cause no row returns. So we need another SELECT to get the existing id.

In this SQL, if it fails on conflicts, it will return nothing, then the second SELECT will get the existing row; if it inserts successfully, then there will be two same records, then we need UNION to merge the result.


This solution works well and avoids doing an unnecessary write (update) to the DB!! Nice!
Woo... Thanks, mate. Thanks a ton. This worked like a charm. I had a dependency where I need the ids to be inserted in another CTE.
It doesn't deal with the technical details of the original question - but it actually gets the job done nicely. Simple and adaptable - tnx!
J
Jaumzera

Upsert, being an extension of the INSERT query can be defined with two different behaviors in case of a constraint conflict: DO NOTHING or DO UPDATE.

INSERT INTO upsert_table VALUES (2, 6, 'upserted')
   ON CONFLICT DO NOTHING RETURNING *;

 id | sub_id | status
----+--------+--------
 (0 rows)

Note as well that RETURNING returns nothing, because no tuples have been inserted. Now with DO UPDATE, it is possible to perform operations on the tuple there is a conflict with. First note that it is important to define a constraint which will be used to define that there is a conflict.

INSERT INTO upsert_table VALUES (2, 2, 'inserted')
   ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key
   DO UPDATE SET status = 'upserted' RETURNING *;

 id | sub_id |  status
----+--------+----------
  2 |      2 | upserted
(1 row)

Nice way to always get the affected row id, and know whether it was an insert or upsert. Just what I needed.
This is still using the "Do Update", which the disadvantages have already been discussed.
J
João Haas

For insertions of a single item, I would probably use a coalesce when returning the id:

WITH new_chats AS (
    INSERT INTO chats ("user", "contact", "name")
    VALUES ($1, $2, $3)
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
) SELECT COALESCE(
    (SELECT id FROM new_chats),
    (SELECT id FROM chats WHERE user = $1 AND contact = $2)
);

For insertions of multiples items, you can put the values on a temporary WITH and reference them later:

WITH chats_values("user", "contact", "name") AS (
    VALUES ($1, $2, $3),
           ($4, $5, $6)
), new_chats AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT * FROM chat_values
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
) SELECT id
    FROM new_chats
   UNION
  SELECT chats.id
    FROM chats, chats_values
   WHERE chats.user = chats_values.user
     AND chats.contact = chats_values.contact;

Note: as per Erwin's comment, on the off-chance your application will try to 'upsert' the same data concurrently (two workers trying to insert <unique_field> = 1 at the same time), and such data does not exist on the table yet, you should change the isolation level of your transaction before running the 'upsert':

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

On that specific case, one of the two transactions will be aborted. If this case happens a lot on your application, you might want to just do 2 separate queries, otherwise, handling the error and re-doing the query is just easier and faster.


Important to rename to the Coalesce to id. ... SELECT COALESCE ( ... ) AS id
@Madacol agree that you should add it if you want to have 100% 'compliant' verison of 'INSERT ... RETURNING ...', but most of the times the result is going through a SQL client, which ignores column names. Leaving as is for simplicity.
Not only less DB impact (avoiding locks & writes), but this COALESCE approach notably boosted performance and is still easy to read. Great solution!
My favorite solution
Not safe under concurrent write load. You would need to start a new query to see rows that have been committed concurrently.
r
reads0520

Building on Erwin's answer above (terrific answer btw, never would have gotten here without it!), this is where I ended up. It solves a couple additional potential problems - it allows for duplicates (which would otherwise throw an error) by doing a select distinct on the input set, and it ensures that the returned IDs exactly match the input set, including the same order and allowing duplicates.

Additionally, and one part that was important for me, it significantly reduces the number of unnecessary sequence advancements using the new_rows CTE to only try inserting the ones that aren't already in there. Considering the possibility of concurrent writes, it will still hit some conflicts in that reduced set, but the later steps will take care of that. In most cases, sequence gaps aren't a big deal, but when you're doing billions of upserts, with a high percentage of conflicts, it can make the difference between using an int or a bigint for the ID.

Despite being big and ugly, it performs extremely well. I tested it extensively with millions of upserts, high concurrency, high numbers of collisions. Rock solid.

I've packaged it as a function, but if that's not what you want it should be easy to see how to translate to pure SQL. I've also changed the example data to something simple.

CREATE TABLE foo
(
  bar varchar PRIMARY KEY,
  id  serial
);
CREATE TYPE ids_type AS (id integer);
CREATE TYPE bars_type AS (bar varchar);

CREATE OR REPLACE FUNCTION upsert_foobars(_vals bars_type[])
  RETURNS SETOF ids_type AS
$$
BEGIN
  RETURN QUERY
    WITH
      all_rows AS (
        SELECT bar, ordinality
        FROM UNNEST(_vals) WITH ORDINALITY
      ),
      dist_rows AS (
        SELECT DISTINCT bar
        FROM all_rows
      ),
      new_rows AS (
        SELECT d.bar
        FROM dist_rows d
             LEFT JOIN foo f USING (bar)
        WHERE f.bar IS NULL
      ),
      ins AS (
        INSERT INTO foo (bar)
          SELECT bar
          FROM new_rows
          ORDER BY bar
          ON CONFLICT DO NOTHING
          RETURNING bar, id
      ),
      sel AS (
        SELECT bar, id
        FROM ins
        UNION ALL
        SELECT f.bar, f.id
        FROM dist_rows
             JOIN foo f USING (bar)
      ),
      ups AS (
        INSERT INTO foo AS f (bar)
          SELECT d.bar
          FROM dist_rows d
               LEFT JOIN sel s USING (bar)
          WHERE s.bar IS NULL
          ORDER BY bar
          ON CONFLICT ON CONSTRAINT foo_pkey DO UPDATE
            SET bar = f.bar
          RETURNING bar, id
      ),
      fin AS (
        SELECT bar, id
        FROM sel
        UNION ALL
        TABLE ups
      )
    SELECT f.id
    FROM all_rows a
         JOIN fin f USING (bar)
    ORDER BY a.ordinality;
END
$$ LANGUAGE plpgsql;

A
Aristotle Pagaltzis

If all you want is to upsert a single row

Then you can simplify things rather significantly by using a simple EXISTS check:

WITH
  extant AS (
    SELECT id FROM chats WHERE ("user", "contact") = ($1, $2)
  ),
  inserted AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT $1, $2, $3
    WHERE NOT EXISTS (SELECT NULL FROM extant)
    RETURNING id
  )
SELECT id FROM inserted
UNION ALL
SELECT id FROM extant

Since there is no ON CONFLICT clause, there is no update – only an insert, and only if necessary. So no unnecessary updates, no unnecessary write locks, no unnecessary sequence increments. No casts required either.

If the write lock was a feature in your use case, you can use SELECT FOR UPDATE in the extant expression.

And if you need to know whether a new row was inserted, you can add a flag column in the top-level UNION:

SELECT id, TRUE AS inserted FROM inserted
UNION ALL
SELECT id, FALSE FROM extant

I'm getting the error: Caused by: org.postgresql.util.PSQLException: ERROR: INSERT has more target columns than expressions Hint: The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses?
The hint already said exactly what was wrong. Fixed; dunno why I put those parentheses in there.
P
Paul Draper

The simplest, most performant solution is

BEGIN;

INSERT INTO chats ("user", contact, name) 
    VALUES ($1, $2, $3), ($2, $1, NULL) 
ON CONFLICT ("user", contact) DO UPDATE
  SET name = excluded.name
  WHERE false
RETURNING id;

SELECT id
FROM chats
WHERE (user, contact) IN (($1, $2), ($2, $1));

COMMIT;

The DO UPDATE WHERE false locks but does not update the row, which is a feature, not a bug, since it ensures that another transaction cannot delete the row.

Some comments have want to distinguish between updated and created rows.

In that case, simply add txid_current() = xmin AS created to the select.


Why do you even need the DO UPDATE..WHERE false and RETURNING clauses if you're just returning the insertion set ids in the SELECT? In PG 12 the RETURNING clause still returns nothing if there's no UPDATE (per the WHERE false clause)
@BrDaHa, I explained that: "locks but does not update the row... it ensures that another transaction cannot delete the row"
Yes, you said "DO UPDATE WHERE false locks but does not update the row", I get that part. I was asking why the RETURNING clause is there, when it doesn't actually return anything. Is the RETURNING clause is also needed to prevent deletions?
@BrDaHa, oh, yes, it's been a long time since I've looked at this, but I think returning is unnecessary.
See dba.stackexchange.com/a/212634/4719 if you have multiple unique constraints
C
ChoNuff

I modified the amazing answer by Erwin Brandstetter, which won't increment the sequence, and also won't write-lock any rows. I'm relatively new to PostgreSQL, so please feel free to let me know if you see any drawbacks to this method:

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, new_rows AS (
   SELECT 
     c.usr
     , c.contact
     , c.name
     , r.id IS NOT NULL as row_exists
   FROM input_rows AS r
   LEFT JOIN chats AS c ON r.usr=c.usr AND r.contact=c.contact
   )
INSERT INTO chats (usr, contact, name)
SELECT usr, contact, name
FROM new_rows
WHERE NOT row_exists
RETURNING id, usr, contact, name

This assumes that the table chats has a unique constraint on columns (usr, contact).

Update: added the suggested revisions from spatar (below). Thanks!

Yet another update, per Revinand comment:

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, new_rows AS (
   INSERT INTO chats (usr, contact, name)
   SELECT 
     c.usr
     , c.contact
     , c.name
   FROM input_rows AS r
   LEFT JOIN chats AS c ON r.usr=c.usr AND r.contact=c.contact
   WHERE r.id IS NULL
   RETURNING id, usr, contact, name
   )
SELECT id, usr, contact, name, 'new' as row_type
FROM new_rows
UNION ALL
SELECT id, usr, contact, name, 'update' as row_type
FROM input_rows AS ir
INNER JOIN chats AS c ON ir.usr=c.usr AND ir.contact=c.contact

I haven't tested the above, but if you're finding that the newly inserted rows are being returned multiple times, then you can either change the UNION ALL to just UNION, or (better), just remove the first query, altogether.


Instead of CASE WHEN r.id IS NULL THEN FALSE ELSE TRUE END AS row_exists just write r.id IS NOT NULL as row_exists. Instead of WHERE row_exists=FALSE just write WHERE NOT row_exists.
Good solution, but it doesn't answer the question. Your solution returns only inserted rows
@Revinand good point; added the full query beneath.