Postgres从其他表中插入值 [英] Postgres insert value from insert in other table

问题描述

我有两个表：

CREATE TABLE tbl_a (
id serial primary key NOT NULL,
name text NOT NULL,
tbl_b_reference NOT NULL
)

CREATE TABLE tbl_b (
id serial primary key NOT NULL,
status text)

我想做两次插入。在tbl_b中插入一个，然后在将我插入到tbl_a中时使用该插入中的id。

I want to do two inserts. One in tbl_b, and then use the id from that insert when I do my insert into tbl_a.

我已经尝试过：

INSERT INTO tbl_a(name, tbl_b_reference) 
VALUES ("myName", (INSERT INTO tbl_b (status) VALUES ('OK') RETURNING id));

但我只得到语法错误，指向第二个 INTO

but I only get a syntax error pointing at the second "INTO"

ERROR: syntax error at or near "INTO" Position: 68

我从哪里去，有没有编写永久函数或创建触发器的可能？我是Postgres的新手，只了解MySQL / MariaDB的一些基础知识。我一直在这里搜索与嵌套插入相关的其他问题，但找不到我可以实际使用的东西，因此代码示例将不胜感激。

Where do I go from here and is it possible to do this without writing permanent functions or creating triggers? I'm new to postgres and just know some basics of MySQL/MariaDB. I've been searching around here for other questions related to nested inserts but couldn't find something that I managed to actually use, so code examples would be much appreciated.

推荐答案

对于这种插入链接，您需要一个公共表表达式：

You need a common table expression for this kind of insert chaining:

with ta as (
  INSERT INTO tbl_b (status) VALUES ('OK') 
  RETURNING id
)
INSERT INTO tbl_a (name, tbl_b_reference) 
VALUES ('myName', (select id from ta));

另一种选择是简单地使用 lastval（）函数引用最后生成的序列值：

Another option is to simply use the lastval() function to reference the last generated sequence value:

INSERT INTO tbl_b (status) VALUES ('OK');
INSERT INTO tbl_a (name, tbl_b_reference) 
  VALUES ('myName', lastval());

请注意，您不能有任何其他语句在这两个语句之间生成序列值。

Note that you must not have any other statements that generate sequence values between those two.

或使用currval（）函数：

Or use the currval() function:

INSERT INTO tbl_b (status) VALUES ('OK');
INSERT INTO tbl_a (name, tbl_b_reference) 
  VALUES ('myName', currval('tbl_b_id_seq'));

'tbl_b_id_seq'是标准名称Postgres用于为序列列创建的序列：

'tbl_b_id_seq' is the standard name Postgres uses for a sequence that is created for a serial column:

这篇关于Postgres从其他表中插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！