PostgreSQL更新查询问题 [英] Problems with a PostgreSQL upsert query
问题描述
我正在尝试通过将新记录更新或插入到 vote_user_table 中来更新数据库。该表的定义如下:
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)
这是我正在使用的查询。我实际上对所有值都使用了参数,但出于测试目的,我将其替换为我确定应该可以使用的值。用户名 aaa@aaa.com 存在。
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
尽管应该插入新值,因为该行在数据库上尚不存在。当我仅使用insert子句手动插入一行,然后执行上面显示的upsert查询时,它正确地更新了该行。
我使用的是PostgreSQL版本9.2.4。
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.
我想到了从另一个问题编写此查询的想法:
插入PostgreSQL中的重复更新吗?
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?
为 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;
推荐答案
UPDATE $在第一个CTE
已更新
中的c $ c>产生无行。这意味着,对于 updated.id
,您也不会获得 NULL
值。加入已更新
时,您什么都不会得到,因此也不会发生 INSERT
的情况。
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.
应该与不存在一起工作:
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
这篇关于PostgreSQL更新查询问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!