如果不存在则插入，否则在 postgresql 中返回 id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18192570/
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
Insert if not exists, else return id in postgresql
提问by aright
I have a simple table in PostgreSQL that has three columns:
我在 PostgreSQL 中有一个简单的表，它有三列：
- id serial primary key
- key varchar
- value varchar
- id 序列主键
- 关键变量
- 值 varchar
I have already seen this question here on SO: Insert, on duplicate update in PostgreSQL?but I'm wondering just how to get the id if it exists, instead of updating. If the standard practice is to always either "insert" or "update if exists", why is that? Is the cost of doing a SELECT (LIMIT 1) greater than doing an UPDATE?
我已经在 SO 上看到过这个问题：插入，在 PostgreSQL 中重复更新？但我想知道如何获取 id 如果它存在，而不是更新。如果标准做法是始终“插入”或“如果存在则更新”，这是为什么呢？执行 SELECT (LIMIT 1) 的成本是否大于执行 UPDATE 的成本？
I have the following code
我有以下代码
INSERT INTO tag
("key", "value")
SELECT 'key1', 'value1'
WHERE
NOT EXISTS (
SELECT id,"key","value" FROM tag WHERE key = 'key1' AND value = 'value1'
);
which works in the sense that it doesn't insert if exists, but I'd like to get the id. Is there a "RETURNING id" clause or something similar that I could tap in there?
从某种意义上说，它不会插入（如果存在），但我想获取 ID。是否有“返回 ID”子句或类似的东西我可以在那里点击？
回答by Clodoaldo Neto
Yes there is returning
就在这里 returning
INSERT INTO tag ("key", "value")
SELECT 'key1', 'value1'
WHERE NOT EXISTS (
SELECT id, "key", "value"
FROM node_tag
WHERE key = 'key1' AND value = 'value1'
)
returning id, "key", "value"
To return the row if it already exists
如果该行已存在，则返回该行
with s as (
select id, "key", "value"
from tag
where key = 'key1' and value = 'value1'
), i as (
insert into tag ("key", "value")
select 'key1', 'value1'
where not exists (select 1 from s)
returning id, "key", "value"
)
select id, "key", "value"
from i
union all
select id, "key", "value"
from s
If the row does not exist it will return the inserted one else the existing one.
如果该行不存在，它将返回插入的行，否则返回现有行。
BTW, if the pair "key"/"value" makes it unique then it is the primary key, and there is no need for an id column. Unless one or both of the "key"/"value" pair can be null.
顺便说一句，如果“键”/“值”对使它唯一，那么它就是主键，并且不需要 id 列。除非“键”/“值”对中的一个或两个都可以为空。
回答by Roman Pekar
with vals as (
select 'key5' as key, 'value2' as value
)
insert into Test1 (key, value)
select v.key, v.value
from vals as v
where not exists (select * from Test1 as t where t.key = v.key and t.value = v.value)
returning id
回答by Wutikrai
And you can store value returned to variables in form of ... RETURNING field1, field2,... INTO var1, var2,...
您可以将返回给变量的值以 ... RETURNING field1, field2,... INTO var1, var2,... 的形式存储
RETURNING will normally return a query which would return Error 'query has no destination for result data' if you call it in plpgsql without using its returned result set.
RETURNING 通常会返回一个查询，如果您在 plpgsql 中调用它而不使用其返回的结果集，则该查询将返回错误“查询没有结果数据的目的地”。
