0

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?

3
  • Why the MySQL tag?
    – user1822
    May 15, 2020 at 5:38
  • 1
    Unrelated to your question, but: serial should be avoided in modern Postgres version. Use identity instead
    – user1822
    May 15, 2020 at 5:39
  • mysql has auto-increment column too.
    – jayarjo
    May 15, 2020 at 6:19

1 Answer 1

2

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

insert into some_table
default values;

Online example

11
  • 1
    For MySQL it will be insert into test (id) values (null);
    – Akina
    May 15, 2020 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.
    – user1822
    May 15, 2020 at 6:14
  • In MySQL inserting NULL value into autoincremented field causes next value generation.
    – Akina
    May 15, 2020 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, 2020 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, 2020 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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