Menu
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!
271
First thing to do is specify the table name followed by the columns where you want to insert the data or rows. Secondly, you must list a comma-separated value list after the VALUES clause. The value list must be in the same order as the columns list specified after the table name.
Here is an example of how to use the PostgreSQL IS NOT NULL condition in a SELECT statement: SELECT * FROM employees WHERE first_name IS NOT NULL; This PostgreSQL IS NOT NULL example will return all records from the employees table where the first_name does not contain a null value.
In a table definition, default values are listed after the column data type. For example: CREATE TABLE products ( product_no integer, name text, price numeric DEFAULT 9.99 ); The default value can be an expression, which will be evaluated whenever the default value is inserted (not when the table is created).
PostgreSQL INSERT Multiple Rows First, specify the name of the table that you want to insert data after the INSERT INTO keywords. Second, list the required columns or all columns of the table in parentheses that follow the table name. Third, supply a comma-separated list of rows after the VALUES keyword.
I found that there is special syntax for this exact use-case:
INSERT INTO test_table DEFAULT VALUES;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With