Problems with a PostgreSQL upsert query
common-table-expression, postgresql, postgresql-9.2, sql, upsert
Solution
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
Problem
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; ```