这是一个经常出现的问题，即在可能的并发写入负载下，与
UPSERT（即
INSERT或
UPDATE）相关但不同的
SELECT或INSERT问题。
这个PL/pgSQL函数使用
UPSERT（INSERT ... ON CONFLICT ..）来
INSERT或
SELECT一个
单行。
CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int)
LANGUAGE plpgsql AS
$func$
BEGIN
SELECT tag_id
FROM tag
WHERE tag = _tag
INTO _tag_id;
IF NOT FOUND THEN
INSERT INTO tag AS t (tag)
VALUES (_tag)
ON CONFLICT (tag) DO NOTHING
RETURNING t.tag_id
INTO _tag_id;
END IF;
END
$func$;
还有一个极小的时间窗口可以发生竞态条件。为了
绝对确定我们获得一个ID：
CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int)
LANGUAGE plpgsql AS
$func$
BEGIN
LOOP
SELECT tag_id
FROM tag
WHERE tag = _tag
INTO _tag_id;
EXIT WHEN FOUND;
INSERT INTO tag AS t (tag)
VALUES (_tag)
ON CONFLICT (tag) DO NOTHING
RETURNING t.tag_id
INTO _tag_id;
EXIT WHEN FOUND;
END LOOP;
END
$func$;
fiddle
这将循环执行，直到INSERT或SELECT成功为止。
调用：
SELECT f_tag_id('possibly_new_tag');
关于
EXIT：
如果同一事务中的后续命令依赖于该行的存在，并且实际上可能有其他事务同时更新或删除它，您可以在SELECT语句中使用FOR SHARE锁定现有行。
如果插入了该行，则它将被锁定（或对其他事务不可见）直到事务结束。
从常见情况（INSERT vs SELECT）开始，以提高速度。
相关：
与INSERT或SELECT一次性插入（一组）多行的相关（纯SQL）解决方案：
这个纯SQL解决方案有什么问题？
CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int)
LANGUAGE sql AS
$func$
WITH ins AS (
INSERT INTO tag AS t (tag)
VALUES (_tag)
ON CONFLICT (tag) DO NOTHING
RETURNING t.tag_id
)
SELECT tag_id FROM ins
UNION ALL
SELECT tag_id FROM tag WHERE tag = _tag
LIMIT 1;
$func$;
不完全错误，但它未能封堵一个漏洞，就像
@FunctorSalad解决的那样。如果并发事务在同一时间尝试执行相同操作，该函数可能会得到一个空结果。
手册:
所有语句都在同一个快照下执行
如果一个并发事务在稍早的时候插入了相同的新标签，但尚未提交：
我们什么都得不到。这不是预期的结果。这与直觉逻辑相悖（我也曾陷入其中），但这是Postgres的MVCC模型的工作方式 - 必须如此。
所以如果多个事务尝试在同一时间插入相同的标签，请不要使用此方法。或者循环直到实际获得一行。在常见的工作负载中，循环几乎不会触发。
Postgres 9.4或更早版本
给定这个（稍微简化的）表：
CREATE table tag (
tag_id serial PRIMARY KEY
, tag text UNIQUE
);
一个几乎100%安全的函数，用于插入新标签/选择现有标签，可能是这样的。
CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT tag_id int)
LANGUAGE plpgsql AS
$func$
BEGIN
LOOP
BEGIN
WITH sel AS (SELECT t.tag_id FROM tag t WHERE t.tag = _tag FOR SHARE)
, ins AS (INSERT INTO tag(tag)
SELECT _tag
WHERE NOT EXISTS (SELECT 1 FROM sel)
RETURNING tag.tag_id)
SELECT sel.tag_id FROM sel
UNION ALL
SELECT ins.tag_id FROM ins
INTO tag_id;
EXCEPTION WHEN UNIQUE_VIOLATION THEN
RAISE NOTICE 'It actually happened!';
END;
EXIT WHEN tag_id IS NOT NULL;
END LOOP;
END
$func$;
db<>fiddle 这里
旧版 sqlfiddle
为什么不是100%？请考虑手册中相关UPSERT示例的注释：
解释
先尝试使用SELECT。这样你可以99.99%的时间避免使用代价更高的异常处理。
使用CTE来最小化（已经很小的）竞争条件的时间窗口。
SELECT和INSERT之间的时间窗口在一个查询中非常短暂。如果你没有大量并发负载，或者如果你可以接受一年一次的异常，你可以忽略这种情况并使用更快的SQL语句。
不需要使用FETCH FIRST ROW ONLY（= LIMIT 1）。标签名明显是UNIQUE。
如果你的表tag通常没有并发的DELETE或UPDATE，可以在我的示例中去掉FOR SHARE。这会稍微降低一点性能。
不要引用语言名称：'plpgsql'。 plpgsql是一个标识符。引用可能会引起问题，只有为了向后兼容才被容忍。
不要使用非描述性的列名，比如id或name。当在关系型数据库中连接多个表时，你会得到多个相同的列名，必须使用别名。
嵌入到您的函数中
使用此函数，您可以大大简化您的FOREACH LOOP为：
...
FOREACH TagName IN ARRAY $3
LOOP
INSERT INTO taggings (PostId, TagId)
VALUES (InsertedPostId, f_tag_id(TagName));
END LOOP;
...
更快的方法是使用一个单独的SQL语句，使用
unnest()函数：
INSERT INTO taggings (PostId, TagId)
SELECT InsertedPostId, f_tag_id(tag)
FROM unnest($3) tag;
替换整个循环。
替代方案
这个变体基于带有LIMIT子句的UNION ALL的行为：一旦找到足够的行，剩下的部分就不会执行：
- [多个SELECT尝试直到有结果的方法](link1)
在此基础上，我们可以将INSERT外包到一个单独的函数中。只有在那里我们需要异常处理。与第一个解决方案一样安全。
CREATE OR REPLACE FUNCTION f_insert_tag(_tag text, OUT tag_id int)
RETURNS int
LANGUAGE plpgsql AS
$func$
BEGIN
INSERT INTO tag(tag) VALUES (_tag) RETURNING tag.tag_id INTO tag_id;
EXCEPTION WHEN UNIQUE_VIOLATION THEN
END
$func$;
主函数中使用了哪个？
CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int)
LANGUAGE plpgsql AS
$func$
BEGIN
LOOP
SELECT tag_id FROM tag WHERE tag = _tag
UNION ALL
SELECT f_insert_tag(_tag)
LIMIT 1
INTO _tag_id;
EXIT WHEN _tag_id IS NOT NULL;
END LOOP;
END
$func$;
这样做会更便宜一些，如果大部分的调用只需要
SELECT，因为包含
EXCEPTION子句的较昂贵的
INSERT块很少被执行。查询也更简单。
这里不允许使用
FOR SHARE（在
UNION查询中不允许）。
LIMIT 1在这里是不必要的（在pg 9.4中进行了测试）。Postgres会根据
INTO _tag_id推导出
LIMIT 1，并且只执行直到找到第一行为止。
SELECT或INSERT问题，只不过我将其作为独立语句执行，而不是作为函数执行。然而，有时我会从语句中得到空结果。在其他事务已经插入冲突行的情况下，就会出现这种情况。但是，从表中选择后续行却能得到结果。我认为这不是一种预期行为，难道不是吗？ - twoflowerSELECT行时，并发事务尚未提交。可以通过像上面建议和解释的那样使用ON CONFLICT (tag) DO UPDATE SET tag = t.tag WHERE FALSE来避免该问题。您试过了吗？ - Erwin Brandstetter