Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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")
.returningResult(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
.returningResult(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 returningResult() clause.
Dialect support
This example using jOOQ:
insertInto(AUTHOR, AUTHOR.LAST_NAME).values("Doe").returningResult(AUTHOR.ID)
Translates to the following dialect specific expressions:
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Derby, Exasol, HSQLDB, Hana, 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')
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!