Default Values

A column can be assigned a default value to be used when a new row doesn’t specify a value for it. In a table definition, default values are listed after the column data type.

The default value can be an expression which will be evaluated whenever the new row is inserted e.g CURRENT_DATE.

mydb=> CREATE TABLE animal_products (
mydb(>   name           text,
mydb(>   perishable     bool DEFAULT 'true',
mydb(>   date_produced  date DEFAULT CURRENT_DATE
mydb(> );
CREATE TABLE
mydb=> INSERT INTO animal_products (name) VALUES ('Mutton');
INSERT 0 1
mydb=> INSERT INTO animal_products (name, perishable, date_produced)
mydb->   VALUES ('Leather', 'false', '2023-07-15');
INSERT 0 1

mydb=> SELECT * FROM animal_products;  -- 'Mutton' row was filled with defaults
  name   | perishable | date_produced 
---------+------------+---------------
 Mutton  | t          | 2023-08-03
 Leather | f          | 2023-07-15
(2 rows)

If no default value is explicitly declared, the default value is NULL.

mydb=> INSERT INTO animal_products (perishable) VALUES ('f');  -- use default name (NULL) & date
INSERT 0 1
mydb=> SELECT * FROM animal_products;
  name   | perishable | date_produced 
---------+------------+---------------
 Mutton  | t          | 2023-08-03
 Leather | f          | 2023-07-15
         | f          | 2023-08-03
(3 rows)

mydb=> DELETE FROM animal_products WHERE name IS NULL;
DELETE 1