all that is technology

getGeneratedKeys with PostgreSQL


In a complex SQL transaction where the tables are normalized and split, it becomes necessary to put primary key of first tables' insert as a foreign key of subsequent tables' inserts. Though java.sql.PreparedStatement have getGeneratedKeys, it works pretty different for PostgreSQL than MySQL.

The following code block shows the addition of Statement.RETURN_GENERATED_KEYS which is essential for PostgreSQL

String insertSql = "INSERT INTO table1 (c1, c2, c3) VALUES (?, ?, ?)";
PreparedStatement insertPs = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);


ResultSet rsKey = insertPs.getGeneratedKeys();

Long insertKey = null;

if( {
    insertKey = rsKey.getLong(1);

insertKey is the primary key created for the corresponding insert, which can be used for the subsequent inserts.

As far as SQL is concerned.

  id serial NOT NULL,
  c1 character varying(500) NOT NULL,
  c2 character varying(500) NOT NULL,
  c3 character varying(500) NOT NULL,
  CONSTRAINT table1_pkey PRIMARY KEY (id)