DEFAULT Values

A DEFAULT value is used for a column when an insert does not give it a value. It saves you from typing the same value again and again, and it avoids unwanted NULL values.

This is useful for columns like a status, a created date or a true/false flag.


Syntax

CREATE TABLE table_name (
  column_name datatype DEFAULT default_value
);

For a table that already exists:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

Example: Tasks with a Default Status

CREATE TABLE tasks (
  task_id INT PRIMARY KEY,
  title VARCHAR(100),
  status VARCHAR(20) DEFAULT 'open',
  done BOOLEAN DEFAULT FALSE
);

INSERT INTO tasks (task_id, title)
VALUES (1, 'Write report');

INSERT INTO tasks
VALUES (2, 'Call Bob', 'closed', TRUE);

SELECT * FROM tasks;

Output:

CREATE TABLE
INSERT 0 1
INSERT 0 1
 task_id |    title     | status | done 
---------+--------------+--------+------
       1 | Write report | open   | f
       2 | Call Bob     | closed | t
(2 rows)

The first task did not give a status or done, so it got the defaults: 'open' and false. psql shows false as f and true as t.

The second task gave its own values, so the defaults were not used.


Example: Add a Default to an Existing Table

ALTER TABLE employees
ALTER COLUMN gender SET DEFAULT 'unknown';

INSERT INTO employees (id, name, age, department)
VALUES (21, 'Kai', 26, 'IT');

SELECT * FROM employees WHERE id = 21;

Output:

ALTER TABLE
INSERT 0 1
 id | name | age | department | gender  
----+------+-----+------------+---------
 21 | Kai  |  26 | IT         | unknown
(1 row)

Key Points

  • A default is used only when the insert leaves the column out.
  • If you give a value, even NULL, the default is not used.
  • CURRENT_DATE is a common default for date columns. It fills in today's date.

In the exercise, give a column a default and then insert a row that uses it.