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
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!