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_DATEis 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.