Suppose I have a table with a serial as primary key and couple of other columns, that are all nullable. Is there any way to insert a new row in that table by omitting all the columns in the insert clause? Since serial is auto-incremental and can be omitted right? The rest of columns can be null and can therefore be omitted too? So theoretically it should be possible somehow. But is it?
1 Answer
Yes, you can use the default values clause (at least in Postgres):
insert into some_table
default values;
-
1
-
@Akina But that would insert a NULL value in the
idcolumn. That should beinsert into test (id) values (default)if you do need to list a column.– user1822May 15, 2020 at 6:14 -
In MySQL inserting NULL value into autoincremented field causes next value generation.– AkinaMay 15, 2020 at 6:16
-
1That should be
insert into test (id) values (default)if you do need to list a column It is correct too. If you not list the column then you must adddefaultfor each field:insert into test values (default, default, default)- if not then "the amount of values doesn't match the amount of columns" error will be generated.– AkinaMay 15, 2020 at 6:19 -
1Using AUTO_INCREMENT: (if) No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. ... If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.– AkinaMay 15, 2020 at 6:30
serialshould be avoided in modern Postgres version. Useidentityinstead