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