CODEWITHSUNDEEP
sqlpostgresqlsql-insert
Robin
Robin

Reputation: 1967

Return id from postgresql insertion

In an external application I want to do the following:

  1. Insert an entry to table A
  2. Insert a bunch of entries to table B, with the id of my newly inserted item in A as a foreign key

So the tables would look like this:

A(_id, data)

B(_id, other_data)
     _id --> A._id

Is this possible to accomplish in only postgresql? Or is it possible to return the id to my application after the item in table A was created, so my application can add the rest of the values?

I've looked at the following post, but it has an incorrect syntax and it causes an infinite loop resulting in a stack overflow (how ironic).

Upvotes: 0

Views: 2489

Answers (1)

user330315
user330315

Reputation:

There are several ways to do it:

Assuming a._id is a serial column:

insert into a (data) values ('foo');
insert into b (_id, other_data) values (lastval(), 'foobar');

Edit (after discussion in comments): Note that lastval() is concurrency safe (as all sequence related functions). Even if other sessions insert something into a between the two statements, lastval() would still return the value relevant for the current session (i.e. the one that was generated by the previous insert)

Other ways of doing that are described in detail in the question you already linked to: https://stackoverflow.com/a/6560530/330315

Or using a data modifying CTE:

with insert_a as (
    insert into a (data) values ('foo')
    returning _id
)
insert into b (_id, other_data)
values 
  ((select _id from insert_a), 'one'), 
  ((select _id from insert_a), 'two'), 
  ((select _id from insert_a), 'three');

Upvotes: 4

Related Questions