CODEWITHSUNDEEP
sqlpostgresql
Robert C. Holland
Robert C. Holland

Reputation: 1813

How to insert autoincrementing id from one table into another in one command (using returning)?

I've attempted the following:

INSERT INTO second_table(id, somethingelse)
VALUES(
       (INSERT INTO first_table(post_text) VALUES('a text') RETURNING id),
       'abcd123'
    );

I know how to do select with 2 query, but I'm trying to avoid any concurrency issue by doing these 2 insertions at once. Above gives me the following error:

ERROR:  syntax error at or near "INTO"
LINE 3:         (INSERT INTO first_table(post_text) VALUES('a text...

I'm trying to insert auto-incrementing id from first_table into second_table when a row is inserted into first_table.

Upvotes: 0

Views: 41

Answers (3)

SynozeN Technologies
SynozeN Technologies

Reputation: 1347

You need to run below command to off identity column Set Identity_Insert [TableName] Off

Upvotes: 0

Lohit Gupta
Lohit Gupta

Reputation: 1081

Try creating a trigger to be executed whenever a row is inserted in first table. something like:

CREATE TRIGGER trigger_name AFTER INSERT OF post_text ON first_table [ INSERT INTO second_table(id, somethingelse) VALUES( (select id from first_table where post_text='a text'), 'abcd123' ); ];

Upvotes: 1

user330315
user330315

Reputation:

Use a data modifying CTE:

with first_insert as (
  INSERT INTO first_table(post_text) 
  VALUES('a text') 
  RETURNING id
)
INSERT INTO second_table(id, somethingelse)
select id, 'abcd123'
from first_insert;

Upvotes: 1

Related Questions