How do I insert an empty row, but have the autonumber update correctly?

Question

I was hoping to be able to do something like

INSERT INTO `table`;

or maybe even a SET clause like

SET `primary_key` = null

where all the columns of the table aren't set (and are left to their default), with the exception of the autonumbered primary key which should be set.

Any suggestions?

`INSERT INTO table (non, primary, key, columns) VALUES (NULL, NULL, NULL, NULL);` (assuming the columns are nullable). — Brad Christie, May 27 '11 at 19:42

score 47 · Accepted Answer · answered May 27 '11 at 19:41

47

INSERT INTO `table` (`primary_key`) VALUES (NULL);

answered May 27 '11 at 19:41

Dan Simon

12,891
3
49
55

1

Ah. That's what I was doing before, but getting an error. Turns out I had the primary key set wrong. Sigh. Thanks! – Shane May 27 '11 at 19:50
1

See Linus Unnebäck's answer below for postgres 12, this no longer seems to work – Justin Furuness Jun 13 '21 at 03:07

score 45 · Answer 2 · answered May 27 '11 at 19:43

45

INSERT INTO `table` () VALUES();

answered May 27 '11 at 19:43

Joe Stefanelli

132,803
19
237
235

It is nicely simple. – user3422161 Mar 09 '16 at 17:25
See Linus Unnebäck's answer below for postgres 12, this no longer seems to work – Justin Furuness Jun 13 '21 at 03:08
@JustinFuruness Just tried with MySQL Version 10.3.32-MariaDB-cll-lve and all works. – Andris Feb 14 '22 at 05:13
1

@Andris I was more so referring to Postgres12 specifically. Great that it works with MySQL still! – Justin Furuness Feb 14 '22 at 17:58

score 35 · Answer 3 · edited May 23 '17 at 11:54

35

I believe that the more correct answer would be this:

INSERT INTO my_table DEFAULT VALUES

As suggested here: https://stackoverflow.com/a/13605273/148072

edited May 23 '17 at 11:54

Community

1
1

answered Jan 21 '17 at 12:34

Linus Unnebäck

23,234
15
74
89

5

Only answer here that worked for me (Postgres 12). – Colin McDonnell Jun 12 '20 at 06:19
1

Tried `INSERT INTO table_name DEFAULT VALUES;` with MySQL Version 10.3.32-MariaDB-cll-lve and got `SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DEFAULT VALUES' at line 1` – Andris Feb 14 '22 at 05:10

score 7 · Answer 4 · answered Jan 28 '15 at 11:38

7

A little late.. But maybe handy for anyone looking for this answer.

INSERT INTO `table` () VALUES ()

You do not need any specification of the primary key.

answered Jan 28 '15 at 11:38

Alex

1,223
1
19
31

7

Duplicate added 4 years later. – Peter Meadley Mar 21 '18 at 10:02

score 3 · Answer 5 · answered May 27 '11 at 19:43

3

Depends on your table. If it allows null values on every field, just do an insert with all values as null. If it does not allow null values for every field, you will need to insert a row with those fields having some value.

answered May 27 '11 at 19:43

Stephane Gosselin

9,030
5
42
65

How do I insert an empty row, but have the autonumber update correctly?

5 Answers5

Linked