PostgreSql query with INSERT INTO ... RETURNING

Q&A
1

I executed an sql query like this on a postgresql database using pgsql connector:

INSERT INTO tablename (column1,column2) VALUES (‘value1’,‘value2’) RETURNING id

where ‘id’ is a unique identifier (BIGSERIAL) for the record in my database table ( http://www.postgresql.org/docs/9.2/static/sql-insert.html ). I tryed to get the value of ‘id’, but the ResultSet returned by the query is null. If I execute the query in pgAdminIII, I get the correct ‘id’. I use Bonita Studio Community 6.5 on Ubuntu 14.04 64b. Anyone can help me?

2

this is probably a driver issue, have you tried using a Stub JAVA Program to execute a single Insert statement. Does that work and give you what you expect?

If this works then possibly something with the connector, of not then the driver…

regards

3

Thank you for replay. PostgreSql JDBC driver seems okay from version 8.4 http://stackoverflow.com/questions/241003/how-to-get-a-value-from-the-last-inserted-row. Maybe the issue related to use of executeUpdate() instead of executeQuery()? ( https://jdbc.postgresql.org/documentation/92/update.html )

4

Hi Sean McP, I tried to compile a standalone java class that performs INSERT - RETURNIG statement and all works fine. ResultSet is returned not null and I get “id” key with PostgreSQL 9.2 and postgresql-9.2-1002.jdbc4.jar driver. Regards