如何插入通过外键引用另一个 postgres 表的行，如果外行不存在，也创建外行？

Question

在Postgres中，有没有办法以原子方式将一行插入到表中，其中一列引用另一个表，我们查找所需的行是否存在于引用的表中，如果不存在，也插入它？

例如，假设我们有一个美国州表和一个引用州表的城市表：

CREATE TABLE states (
state_id serial primary key,
name text
);
CREATE TABLE cities (
city_id serial,
name text,
state_id int references states(state_id)
);

当我想添加德克萨斯州奥斯汀市时，我希望能够查看德克萨斯州是否存在于州表中，如果是，请在我插入城市表中的新行中使用其state_id。如果州表中不存在德克萨斯州，我想创建它，然后在城市表中使用它的 id。

我尝试了这个查询，但我收到一个错误说

ERROR:  WITH clause containing a data-modifying statement must be at the top level
LINE 2:   WITH inserted AS (
^

WITH state_id AS (
WITH inserted AS (
INSERT INTO states(name)
VALUES ('Texas')
ON CONFLICT DO NOTHING
RETURNING state_id),
already_there AS (
SELECT state_id FROM states
WHERE name='Texas')
SELECT * FROM inserted
UNION
SELECT * FROM already_there)
INSERT INTO cities(name, state_id)
VALUES
('Austin', (SELECT state_id FROM state_id));

我是否忽略了一个简单的解决方案？

Answer 1

这是一个选项：

with inserted as (
insert into states(name) values ('Texas')
on conflict do nothing
returning state_id
)
insert into cities(name, state_id)
values (
'Dallas', 
coalesce(
(select state_id from inserted),
(select state_id from states where name = 'Texas')
)
);

这个想法是尝试插入 CTE，然后在主插入中，检查是否插入了值，否则选择它。

要使其正常工作，您需要对states(name)进行唯一约束：

create table states (
state_id serial primary key,
name text unique
);

DB Fiddlde 上的演示

Answer 2

您可以强制insert语句返回一个值：

WITH inserted AS (
INSERT INTO states (name)
VALUES ('Texas')
ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.NAME
RETURNING state_id
)
. . .

DO UPDATE SET迫使INSERT归还一些东西。

我注意到您没有唯一的约束，因此您还需要：

ALTER TABLE states ADD CONSTRAINT unq_state_name
UNIQUE (name);

否则，ON CONFLICT没有任何可以使用的内容。