upsert and sub select

Question

i have a upsert statement (http://www.the-art-of-web.com/sql/upsert/) doing an insert whenever a row with id does not exist and updating the column when the row exists :

WITH upsert AS 
  (UPDATE foo SET counter=counter+1 WHERE id='bar' RETURNING *) 
  INSERT INTO foo(id, counter) SELECT 'bar', 0
WHERE NOT EXISTS (SELECT * FROM upsert) RETURNING counter;

id is the primary key column (as expected). until here everything works fine.

but there is a 3rd column 'position' which can be used for custom ordering. in case of an update i want to keep the current value.

but the insert statement needs an additional subquery returning the lowest possible position not in use:

WITH upsert AS 
  (UPDATE foo SET counter=counter+1 WHERE id='bar' RETURNING *) 
  INSERT INTO foo(id, counter, position) SELECT 'bar', 0, MIN( position)-1 from foo
WHERE NOT EXISTS (SELECT * FROM upsert) RETURNING counter;

using this statement i get an error

ERROR:  duplicate key value violates unique constraint "id"

whats wrong here ?

How is the constraint "id" defined? Can you paste the output of \d foo — Kouber Saparev, Feb 10 '14 at 18:39
On a side note, your upsert is wrong and does not do what you think it does unless you `LOCK TABLE foo IN EXCLUSIVE MODE` before running it. See http://stackoverflow.com/q/17267417/398670 and its links. If `id` has a `unique` constraint or `primary key` it won't insert duplicates, but it can still fail with a duplicate key error due to a race condition when you run it from multiple connections at once. — Craig Ringer, Feb 11 '14 at 00:06
@KouberSaparev : id is defined as integer with constraint not null default nextval('foo_id_seq'::regclass) — lgersman, Feb 11 '14 at 09:17
@CraigRinger : i just left out the LOCK TABLE STUFF to make my case more easy to read. — lgersman, Feb 11 '14 at 09:18

score 2 · Answer 1 · edited May 23 '17 at 11:58

The problem is that MIN() applied to 0 row returns one row (with a NULL value)

Example:

test=> select min(1) where false;
 min 
-----

(1 row)

This differs from the same WHERE clause without min()

test=> select 1 where false;
 ?column? 
----------
(0 rows)

So when using MIN() in the subquery feeding the INSERT, it will insert a new row even when the WHERE clause evaluates to false, which defeats the logic of this UPSERT.

I think this can be worked around by introducing another subquery:

WITH upsert AS 
  (UPDATE foo SET counter=counter+1 WHERE id='bar' RETURNING *) 
  INSERT INTO foo(id, counter, position) 
   SELECT * FROM (SELECT 'bar', 0, MIN( position)-1 from foo) s
            WHERE NOT EXISTS (SELECT * FROM upsert)
   RETURNING counter;

Note however that cramming this into a single SQL statement does not confer any guarantee of systematic success when run concurrently.

See for more:
How do I do an UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?

upsert and sub select

1 Answers1