CODEWITHSUNDEEP
sqlstringpostgresqlsql-insertcommon-table-expression
Julia.T
Julia.T

Reputation: 139

Insert result from one insert query into another insert

I have two psotgres tables. Table 1 has userid as primary key. Table 2 uses the userid as a foreign key, I need to insert data into table 1 and 2. I want to do it all in one statement If the insert into table 2 fails I need the insert into table 1 two be deleted.

INSERT into table1 (userid,email)Values(12,"[email protected]") RETURNING userid

INSERT into table2(userid,name,address)Values(12,"Joe","123 test Dr")

How can I do this in one statement?

Upvotes: 0

Views: 736

Answers (1)

GMB
GMB

Reputation: 222412

You just need to wrap the first insert in a with clause. If you know all values in advance, you don't even need returning:

with ins as (
    insert into table1 (userid, email)
    values(12,'[email protected]') 
)
insert into table2 (userid, name, address)
values(12, 'Joe', '123 test Dr')

On the other hand, if userid is an autogenerated value, then you can return it from the CTE, and use it in the other insert as follows:

with ins as (
    insert into table1 (email)
    values('[email protected]') 
    returning userid
)
insert into table2 (userid, name, address)
select userid, 'joe', '123 test Dr'
from ins

Side note: use single quotes for literal strings. Double quotes are used for identifiers (column names, table names, and so on).

Upvotes: 2

Related Questions