Postgres insert value from insert in other table

Question

I have two tables:

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)

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.

I've tried this:

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

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

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

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.

Also: tbl_b_reference NOT NULL needs to be: tbl_b_reference integer NOT NULL references tbl_b — user330315
– user330315, Commented Sep 20, 2017 at 10:01
@a_horse_with_no_name The last comment did the trick! Thank you for your help! — Erik Einarsson
– Erik Einarsson, Commented Sep 20, 2017 at 10:26
Related: PostgreSQL nested INSERTs / WITHs for foreign key insertions — unutbu
– unutbu, Commented Mar 8, 2019 at 20:56

user330315 · Accepted Answer · 2017-09-20 10:01:08Z

10

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));

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.

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' is the standard name Postgres uses for a sequence that is created for a serial column:

answered Sep 20, 2017 at 10:01

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

ZackDeRose Over a year ago

Getting an error "relation \"ta\" does not exist" with this :\

luba · Accepted Answer · 2017-09-20 12:23:42Z

-1

I really like the WITH ... AS solution mentioned above by a_horse_with_no_name since this is the only atomic solution achieving this in one single statement.

If however you feel fine doing it in two statements there's a fourth easy way to do this: with a subquery and max().

INSERT INTO tbl_b (status) VALUES ('OK');
INSERT INTO tbl_a (name, tbl_b_reference) 
  VALUES ('myName', (SELECT max(id) FROM tbl_b));

Personally I find this better than lastval() cause it only depends on this specific sequence not being next()-ed in between the statements and also better than currval() since there's no need to know the sequence name.

And it's the most readable solution too.

edited Sep 20, 2017 at 12:23

answered Sep 20, 2017 at 12:15

luba

1224 bronze badges

Collectives™ on Stack Overflow

Postgres insert value from insert in other table

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related