2

We have a java database abstraction that does a number of inserts for us. At runtime we'll know the table name, the column names to insert and the values. From that we generate a prepared statement and do the insert.

In sql server land we would tack on select id = @@identity to the end of the generated sql to get the newly generated id returned by the query.

Now that we're migrating to postgres this no longer works. It's my understanding that in postgres you can do , 

insert into foo(a, b) values('a', 'b') returning ID

Our problem is that at runtime we don't know the name of the ID column nor do we know the name of the sequence. Is there any way to generically get the value of the newly inserted sequence without knowing the name of the sequence or the name of the column?

dstarh
  • 4,976
  • 5
  • 36
  • 68
  • possible duplicate of [How to get a value from the last inserted row?](http://stackoverflow.com/questions/241003/how-to-get-a-value-from-the-last-inserted-row) – Mark Rotteveel Mar 06 '14 at 10:36
  • Note that the `getGeneratedKeys()` option is the general Java way to do it. The accepted answer on the linked question is not the 'jdbc-way'. – Mark Rotteveel Mar 06 '14 at 10:42
  • @MarkRotteveel not a dupe, in that example you know the name of the column you wan't to return, in our situation we do not. It might be foo_id for one table and bar_id for another table. I've gotten it to work by turning off autocommit and doing two statements in the same connection to get the newly minted id. – dstarh Mar 06 '14 at 14:12
  • How can you insert into tables you know nothing about? At minimum you have queried the metadata and that includes the name of the primary key column. – Mark Rotteveel Mar 06 '14 at 14:16
  • We know the table name and all of the columns we're inserting, however we don't know (at runtime) the name of the primary key column as it's not required to perform the insert. Regardless the answer below works. getGeneratedKeys works in postgres if you know the column name you want back, otherwise you get an exception that no keys were returned. – dstarh Mar 06 '14 at 14:19
  • The default behaviour of the PostgreSQL driver (when using the generated key `create/prepareStatement`-method that accepts the `Statement.RETURN_GENERATED_KEYS` constant and does not accept an array of column names or indexes) is to return **all** columns as internally that will use `RETURNING *`. – Mark Rotteveel Mar 06 '14 at 14:56
  • @MarkRotteveel yea, just reading the source for the driver, and i see that. That still doesn't work for me since I don't know the name of that key. – dstarh Mar 06 '14 at 15:05
  • You can query the metadata [`DatabaseMetaData.getPrimaryKeys`](http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String,%20java.lang.String,%20java.lang.String)) but that is probably just as inefficient as using `lastval()`. – Mark Rotteveel Mar 06 '14 at 15:08
  • Ended up using DatabaseMetaData to get the pkeys and cache them in a static and now can use returning properly. It's a slight bit of overhead but I know for sure when I should adding the returning and when I shouldn't – dstarh Mar 10 '14 at 13:27

1 Answers1

2

If your insert is not triggering further inserts, you can use SELECT LASTVAL(); right after your insert statement

Farlan
  • 1,860
  • 2
  • 28
  • 37
  • lastval() is the last value generated by the connection? – dstarh Mar 05 '14 at 20:19
  • lastval() Return value most recently obtained with nextval for any sequence: http://www.postgresql.org/docs/current/static/functions-sequence.html – Houari Mar 05 '14 at 20:25
  • Either using [`RETURNING`](http://www.postgresql.org/docs/9.3/static/sql-insert.html), or the jdbc `getGeneratedKeys` option – Mark Rotteveel Mar 06 '14 at 10:44
  • it seems that some of the locations where I tried using this did not work getting the error that there was no current value to get in the session. – dstarh Mar 06 '14 at 15:06
  • @dstarh Make sure you are not using autocommit. – Mark Rotteveel Mar 06 '14 at 15:08
  • @MarkRotteveel figured the issue here, if you're entering into an xref/join table and there is no sequence involved it will throw an error – dstarh Mar 06 '14 at 15:26
  • Note this does work but you must ensure a sequence was actually called or it will throw an error – dstarh Mar 10 '14 at 13:46