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?


Yes, you can use the default values clause (at least in Postgres):

insert into some_table
default values;

Online example

  • 1
    For MySQL it will be insert into test (id) values (null);
    – Akina
    May 15 '20 at 5:53
  • @Akina But that would insert a NULL value in the id column. That should be insert into test (id) values (default) if you do need to list a column. May 15 '20 at 6:14
  • In MySQL inserting NULL value into autoincremented field causes next value generation.
    – Akina
    May 15 '20 at 6:16
  • 1
    That 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 add default for 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.
    – Akina
    May 15 '20 at 6:19
  • 1
    Using 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.
    – Akina
    May 15 '20 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.