CODEWITHSUNDEEP
postgresql
JimPri
JimPri

Reputation: 1407

Insert Row into Postgresql Table with Only Default Values

Question: Is there a way to insert a row in PostgreSQL table using the default values for all columns without specifying any column name?

Background: If I have a table with two columns that both have default values (or simply accept NULL), I can omit the column name when I insert a row if I wish the value for that column to be the default value. For instance:

CREATE TABLE test_table ( column1 TEXT, column2 TEXT );

I can insert into the table by only specifying a value for column1 or column2 and the missing column will be populated with the default value (NULL in this case):

INSERT INTO test_table (column1) VALUES ('foo');
INSERT INTO test_table (column2) VALUES ('bar');

The above will result in two rows: [('foo', NULL), (NULL, 'bar')]. However, if I want to use the default value for both columns, it seems that I have to specify at least one column name and explicitly give it the default value. The follow commands are all legal:

INSERT INTO test_table (column1) VALUES (DEFAULT);
INSERT INTO test_table (column2) VALUES (DEFAULT);
INSERT INTO test_table (column1, column2) VALUES (DEFAULT, DEFAULT);

I was unable to create a valid command that allowed me to omit all column names. The following attempts are all illegal:

INSERT INTO test_table;
INSERT INTO test_table () VALUES ();

Is there a way to do this or is it explicitly forbidden? I wasn't able to find any documentation for a case like this. Thanks!

Upvotes: 29

Views: 4995

Answers (1)

JimPri
JimPri

Reputation: 1407

I found that there is special syntax for this exact use-case:

INSERT INTO test_table DEFAULT VALUES;

Upvotes: 45

Related Questions