如果不存在则插入，否则在postgresql中返回id(Insert if not exists, else return id in postgresql)

如果不存在则插入，否则在postgresql中返回id(Insert if not exists, else return id in postgresql)

我在PostgreSQL中有一个简单的表，它有三列：

id串行主键 关键varchar 值varchar

我已经在这里看到这个问题： 在PostgreSQL中重复更新时插入？ 但我想知道如何获取id，如果它存在，而不是更新。 如果标准做法总是“插入”或“更新如果存在”，那为什么呢？ 做一个SELECT（LIMIT 1）的成本比做UPDATE更大吗？

我有以下代码

INSERT INTO tag ("key", "value") SELECT 'key1', 'value1' WHERE NOT EXISTS ( SELECT id,"key","value" FROM tag WHERE key = 'key1' AND value = 'value1' );

它的工作原理是它不会插入，如果存在，但我想得到的id。 有没有一个“RETURNING id”子句或类似的东西，我可以点击在那里？

I have a simple table in PostgreSQL that has three columns:

id serial primary key key varchar value 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?

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?

最满意答案

是的，有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"

如果行已经存在，则返回该行

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

如果行不存在，它将返回已插入的一个。

BTW，如果对“key”/“value”使其唯一，那么它是主键，并且不需要id列。 除非“键”/“值”对中的一个或两个可以为空。

Yes there is 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.