Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

INSERT .. RETURNING

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The Postgres database has native support for an INSERT .. RETURNING clause. This is a very powerful concept that is emulated for all other dialects using JDBC's getGeneratedKeys() method. Take this example:

// Add another author, with a generated ID
Record record =
create.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .values("Charlotte", "Roche")
      .returning(AUTHOR.ID)
      .fetchOne();

System.out.println(record.getValue(AUTHOR.ID));

// For some RDBMS, this also works when inserting several values
// The following should return a 2x2 table
Result<?> result =
create.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .values("Johann Wolfgang", "von Goethe")
      .values("Friedrich", "Schiller")
      // You can request any field. Also trigger-generated values
      .returning(AUTHOR.ID, AUTHOR.CREATION_DATE)
      .fetch();

Some databases have poor support for returning generated keys after INSERTs. In those cases, jOOQ might need to issue another SELECT statement in order to fetch an @@identity value. Be aware, that this can lead to race-conditions in those databases that cannot properly return generated ID values. For more information, please consider the jOOQ Javadoc for the returning() clause.

Dialect support

This example using jOOQ:

insertInto(AUTHOR, AUTHOR.LAST_NAME).values("Doe").returningResult(AUTHOR.ID)

Translates to the following dialect specific expressions:

-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, EXASOL, HANA, HSQLDB, INFORMIX, MEMSQL, MYSQL, ORACLE, REDSHIFT, SNOWFLAKE, 
-- SYBASE, TERADATA, TRINO, VERTICA
INSERT INTO AUTHOR (LAST_NAME)
VALUES ('Doe')

-- AURORA_POSTGRES, COCKROACHDB, DUCKDB, FIREBIRD, POSTGRES, SQLITE, YUGABYTEDB
INSERT INTO AUTHOR (LAST_NAME)
VALUES ('Doe')
RETURNING AUTHOR.ID

-- DB2, H2
SELECT ID
FROM FINAL TABLE (
  INSERT INTO AUTHOR (LAST_NAME)
  VALUES ('Doe')
) AUTHOR

-- MARIADB
INSERT INTO AUTHOR (LAST_NAME)
VALUES ('Doe')
RETURNING ID

-- SQLDATAWAREHOUSE, SQLSERVER
INSERT INTO AUTHOR (LAST_NAME)
OUTPUT inserted.ID
VALUES ('Doe')

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo