postgresql 在一个命令中选择或插入一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6722344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT or INSERT a row in one command
提问by EMP
I'm using PostgreSQL 9.0 and I have a table with just an artificial key (auto-incrementing sequence) and another unique key. (Yes, there is a reason for this table. :)) I want to look up an ID by the other key or, if it doesn't exist, insert it:
我正在使用 PostgreSQL 9.0 并且我有一个只有一个人工键（自动递增序列）和另一个唯一键的表。（是的，这个表是有原因的。:)）我想通过另一个键查找一个 ID，或者，如果它不存在，插入它：
SELECT id
FROM mytable
WHERE other_key = 'SOMETHING'
Then, if no match:
然后，如果没有匹配：
INSERT INTO mytable (other_key)
VALUES ('SOMETHING')
RETURNING id
The question: is it possible to save a round-trip to the DB by doing both of these in one statement? I can insert the row if it doesn't exist like this:
问题：是否可以通过在一个语句中执行这两个操作来保存到数据库的往返行程？如果它不存在，我可以插入该行：
INSERT INTO mytable (other_key)
SELECT 'SOMETHING'
WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING')
RETURNING id
... but that doesn't give the ID of an existing row. Any ideas? There is a unique constraint on other_key, if that helps.
...但这并没有给出现有行的 ID。有任何想法吗？如果有帮助的话，other_key 上有一个唯一约束。
回答by Denis de Bernardy
Have you tried to union it?
你试过联合它吗？
Edit - this requires Postgres 9.1:
编辑 - 这需要 Postgres 9.1：
create table mytable (id serial primary key, other_key varchar not null unique);
WITH new_row AS (
INSERT INTO mytable (other_key)
SELECT 'SOMETHING'
WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING')
RETURNING *
)
SELECT * FROM new_row
UNION
SELECT * FROM mytable WHERE other_key = 'SOMETHING';
results in:
结果是：
id | other_key
----+-----------
1 | SOMETHING
(1 row)
回答by Aloyse
using 9.5 i successfully tried this
使用 9.5 我成功地尝试了这个
- based on Denis de Bernardy's answer
- only 1 parameter
- no union
- no stored procedure
- atomic, thus no concurrency problems (i think...)
- 基于 Denis de Bernardy 的回答
- 只有 1 个参数
- 没有工会
- 没有存储过程
- 原子的，因此没有并发问题（我认为......）
The Query:
查询：
WITH neworexisting AS (
INSERT INTO mytable(other_key) VALUES('hello 2')
ON CONFLICT(other_key) DO UPDATE SET existed=true -- need some update to return sth
RETURNING *
)
SELECT * FROM neworexisting
first call:
第一个电话：
id|other_key|created |existed|
--|---------|-------------------|-------|
6|hello 1 |2019-09-11 11:39:29|false |
second call:
第二个电话：
id|other_key|created |existed|
--|---------|-------------------|-------|
6|hello 1 |2019-09-11 11:39:29|true |
First create your table ;-)
首先创建你的表;-)
CREATE TABLE mytable (
id serial NOT NULL,
other_key text NOT NULL,
created timestamptz NOT NULL DEFAULT now(),
existed bool NOT NULL DEFAULT false,
CONSTRAINT mytable_pk PRIMARY KEY (id),
CONSTRAINT mytable_uniq UNIQUE (other_key) --needed for on conflict
);
回答by Erik Funkenbusch
No, there is no special SQL syntax that allows you to do select or insert. You can do what Ilia mentions and create a sproc, which means it will not do a round trip fromt he client to server, but it will still result in two queries (three actually, if you count the sproc itself).
不，没有允许您执行选择或插入的特殊 SQL 语法。您可以执行 Ilia 提到的操作并创建一个 sproc，这意味着它不会从客户端到服务器进行往返，但仍会产生两个查询（实际上是三个，如果您计算 sproc 本身）。
回答by Ilia Choly
you can use a stored procedure
您可以使用存储过程
IF (SELECT id FROM mytable WHERE other_key = 'SOMETHING' LIMIT 1) < 0 THEN
INSERT INTO mytable (other_key) VALUES ('SOMETHING')
END IF
