SQL DEFAULT
DEFAULT tells the database what to use when an INSERT doesn't supply a value for a column.
At create time
SQL
CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(80) NOT NULL, country CHAR(2) DEFAULT 'AU', active BOOLEAN DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
Triggering the default
The default is used when the column is missing from the INSERT, or you explicitly pass the keyword DEFAULT:
SQL
INSERT INTO customers (id, name) VALUES (1, 'Ada'); -- country='AU', active=1, created_at=NOW() INSERT INTO customers (id, name, country) VALUES (2, 'Ed', DEFAULT); -- country='AU' explicitly
Function defaults
| Use case | Default value |
|---|---|
| Created timestamp | CURRENT_TIMESTAMP |
| Random UUID (PG) | gen_random_uuid() |
| Sequential ID (PG) | nextval('seq') |
| Always 0 / 1 | 0 / 1 |
Add or change later
SQL
ALTER TABLE customers ALTER COLUMN active SET DEFAULT 1; ALTER TABLE customers ALTER COLUMN active DROP DEFAULT;
Tip: Defaults set on a column do not backfill existing rows. Use a one-off
UPDATE for that.Example
Exercise
Default the created_at column to the current time.
created_at DATETIME DEFAULT
Two words joined with an underscore.
Discussion
Loading…