Create row in table with only auto generated fields - SQL
db2, sql
Solution
The SQL standard and most databases support the `DEFAULT VALUES` clause for this:
INSERT INTO "MIGRATION"."VERSION" DEFAULT VALUES;
This is supported in
- CUBRID
- Firebird
- H2
- HSQLDB
- Ingres
- PostgreSQL
- SQLite
- SQL Server
- Sybase SQL Anywhere
If the above is not supported, you can still write this statement as a workaround. In fact, the first is specified by the SQL standard to be equivalent to the second:
INSERT INTO "MIGRATION"."VERSION" (ID, VERSION_DATE) VALUES (DEFAULT, DEFAULT);
This will then also work with:
- Access
- DB2
- MariaDB
- MySQL
- Oracle
For more details, see this blog post here:
http://blog.jooq.org/2014/01/08/lesser-known-sql-features-default-values/
Problem
I've got the following table: ``` CREATE TABLE "MIGRATION"."VERSION" ( ID BIGINT PRIMARY KEY NOT NULL GENERATED BY DEFAULT AS IDENTITY, VERSION_DATE timestamp DEFAULT CURRENT TIMESTAMP NOT NULL ) ``` I would like to insert a "new row" in that table but since everything is autogenerated, how could I do this? ``` INSERT INTO "MIGRATION"."VERSION" VALUES(); ``` isn't working. This has to be done and can't be changed.