Problems with a PostgreSQL upsert query

Question

I'm trying to update the database by either updating or inserting a new record into the vote_user_table. The table is defined as follows:

  Column   |  Type   |                          Modifiers                           
-----------+---------+--------------------------------------------------------------
 id        | integer | not null default nextval('vote_user_table_id_seq'::regclass)
 review_id | integer | not null
 user_id   | integer | not null
 positive  | boolean | default false
 negative  | boolean | default false
Indexes:
    "vote_user_table_pkey" PRIMARY KEY, btree (id)

Here's the query I'm using. I'm actually using parameters for all of the values, but for testing purposes I replaced them with values that I'm sure should work. The username aaa@aaa.com exists.

WITH updated AS (
    UPDATE vote_user_table
    SET
        positive = 'true',
        negative = 'false'
    FROM usuario
    WHERE
        review_id = 6           AND
        user_id = usuario.id    AND
        usuario.username ILIKE 'aaa@aaa.com'
    RETURNING vote_user_table.id
)
INSERT INTO vote_user_table
    (review_id, user_id, positive, negative)
SELECT 6, usuario.id, 'true', 'false'
FROM usuario, updated
WHERE
    updated.id IS NULL AND
    usuario.username ILIKE 'aaa@aaa.com'

The output I obtain after running the query is:

INSERT 0 0

Although it is supposed to insert the new value, because the row doesn't exist on the database yet. When I inserted a row manually, using only the insert clause, and then performed the upsert query shown above, it updated the row correctly.
I'm using PostgreSQL version 9.2.4.

I got the idea of writing this query from this other question:
Insert, on duplicate update in PostgreSQL?

Any ideas on what could I be doing wrong?
Any suggestion on how can I achieve what I want to do?

Create statement for vote_user_table:

CREATE TABLE vote_user_table (
    id integer NOT NULL,
    review_id integer NOT NULL,
    user_id integer NOT NULL,
    positive boolean DEFAULT false,
    negative boolean DEFAULT false
);

CREATE SEQUENCE vote_user_table_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE vote_user_table_id_seq OWNED BY vote_user_table.id;

Do you mind pasting the CREATE TABLE statements for your two tables? — Mike Sherrill 'Cat Recall', Dec 05 '13 at 02:28
Unless you lock the table this can still fail. Please use the upsert loop function provided in the documentation instead; see http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql — Craig Ringer, Dec 05 '13 at 03:23

score 5 · Accepted Answer · edited May 23 '17 at 12:13

The UPDATE in the first CTE updated produces no row. That means, you don't get a NULL value for updated.id either. When joining to updated, you get nothing, so no INSERT happens either.

Should work with NOT EXISTS:

WITH updated AS (
   UPDATE vote_user_table v
   SET    positive = TRUE       -- use booleann values ..
         ,negative = FALSE      -- .. instead of quoted string literals
   FROM   usuario u
   WHERE  v.review_id = 6       -- guessing origin
   AND    v.user_id = u.id
   AND    u.username ILIKE 'aaa@aaa.com'
   RETURNING v.id
   )
INSERT INTO vote_user_table (review_id, user_id, positive, negative)
SELECT 6, u.id, TRUE, FALSE
FROM   usuario u
WHERE NOT EXISTS (SELECT 1 FROM updated)
AND    u.username ILIKE 'aaa@aaa.com';

Be aware that there is still a very tiny chance for a race condition under heavy concurrent load. Details under this related question:
Upsert with a transaction

The query worked! I only had to change _usuario_ to _u_ on the 2nd condition of the update clause. Thank you, I see now what the problem was. I will look into the link you gave me. — enzo, Dec 05 '13 at 14:39

Problems with a PostgreSQL upsert query

1 Answers1

Linked