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.

Haematic

"myName" is a column reference, not a string constant – Lamia

Is this not what foreign keys are for? – Dunk

Also: tbl_b_reference NOT NULL needs to be: tbl_b_reference integer NOT NULL references tbl_b – Lamia

@a_horse_with_no_name The last comment did the trick! Thank you for your help! – Haematic

Related: PostgreSQL nested INSERTs / WITHs for foreign key insertions – Chrisy

Answer 1

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:

Lamia

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

Answer 2

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.

Locution