DEFAULT VALUEDDL
Buy Me A Coffee

Specify a value to be used automatically when no value is provided for a column during INSERT.

Values supplied when no explicit value is given on insert, specified with DEFAULT keyword. Can be literals, expressions, or functions like CURRENT_TIMESTAMP. Adding a default enables NOT NULL on columns previously populated via application logic.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all Literal defaults for any type. Expression defaults (DEFAULT (expr)) supported from 8.0.13. CURRENT_TIMESTAMP as default for DATETIME/TIMESTAMP. ON UPDATE CURRENT_TIMESTAMP auto-updates the column on row modification.
PostgreSQL ✓ Supported all Defaults can be any expression, including function calls. CURRENT_TIMESTAMP, NOW(), gen_random_uuid(), etc. all work. DEFAULT can be set or dropped with ALTER TABLE ... SET DEFAULT / DROP DEFAULT.
SQL Server ✓ Supported all Named DEFAULT constraints are preferred over inline defaults — they can be referenced and dropped by name. GETDATE(), NEWID(), NEWSEQUENTIALID() are common default expressions. DEFAULT keyword can be used in INSERT to explicitly invoke the default.
Oracle ✓ Supported all Standard DEFAULT with literals or expressions. SYSDATE, SYSTIMESTAMP, SYS_GUID() (UUID) as common defaults. 12c+ adds DEFAULT ON NULL — applies the default even when NULL is explicitly inserted (unlike standard DEFAULT which only fires when the column is omitted).
SQLite ✓ Supported all Supports literal defaults and a limited set of functions: CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP. Arbitrary expression defaults are supported in parentheses. Cannot alter defaults after table creation without recreating the table.

Details

Oracle 12c's DEFAULT ON NULL is unique — it fires the default even when NULL is explicitly provided, not just when the column is omitted from INSERT. SQL Server's named DEFAULT constraints are best practice since they can be dropped by name. SQLite does not allow altering defaults post-creation.

Standard Syntax

CREATE TABLE events ( id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status VARCHAR(20) DEFAULT 'pending' );

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

Literal defaults for any type. Expression defaults (DEFAULT (expr)) supported from 8.0.13. CURRENT_TIMESTAMP as default for DATETIME/TIMESTAMP. ON UPDATE CURRENT_TIMESTAMP auto-updates the column on row modification.

CREATE TABLE events ( id INT AUTO_INCREMENT PRIMARY KEY, status VARCHAR(20) DEFAULT 'pending', score INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- Expression default (8.0.13+): CREATE TABLE items (price DECIMAL(10,2), tax DECIMAL(10,2) DEFAULT (price * 0.1));

PostgreSQL

Defaults can be any expression, including function calls. CURRENT_TIMESTAMP, NOW(), gen_random_uuid(), etc. all work. DEFAULT can be set or dropped with ALTER TABLE ... SET DEFAULT / DROP DEFAULT.

CREATE TABLE events ( id SERIAL PRIMARY KEY, status TEXT DEFAULT 'pending', created_at TIMESTAMPTZ DEFAULT NOW(), token UUID DEFAULT gen_random_uuid() ); ALTER TABLE events ALTER COLUMN status SET DEFAULT 'active'; ALTER TABLE events ALTER COLUMN status DROP DEFAULT;

SQL Server

Named DEFAULT constraints are preferred over inline defaults — they can be referenced and dropped by name. GETDATE(), NEWID(), NEWSEQUENTIALID() are common default expressions. DEFAULT keyword can be used in INSERT to explicitly invoke the default.

CREATE TABLE events ( id INT IDENTITY PRIMARY KEY, status NVARCHAR(20) CONSTRAINT df_status DEFAULT 'pending', created_at DATETIME CONSTRAINT df_created DEFAULT GETDATE(), token UNIQUEIDENTIFIER CONSTRAINT df_token DEFAULT NEWID() ); -- Explicit default in INSERT: INSERT INTO events (status, created_at) VALUES (DEFAULT, DEFAULT); -- Drop a named default: ALTER TABLE events DROP CONSTRAINT df_status;

Oracle

Standard DEFAULT with literals or expressions. SYSDATE, SYSTIMESTAMP, SYS_GUID() (UUID) as common defaults. 12c+ adds DEFAULT ON NULL — applies the default even when NULL is explicitly inserted (unlike standard DEFAULT which only fires when the column is omitted).

CREATE TABLE events ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, status VARCHAR2(20) DEFAULT 'pending', created_at TIMESTAMP DEFAULT SYSTIMESTAMP, token RAW(16) DEFAULT SYS_GUID() ); -- 12c+: default even when NULL is explicitly inserted: CREATE TABLE items (qty NUMBER DEFAULT ON NULL 1);

SQLite

Supports literal defaults and a limited set of functions: CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP. Arbitrary expression defaults are supported in parentheses. Cannot alter defaults after table creation without recreating the table.

CREATE TABLE events ( id INTEGER PRIMARY KEY, status TEXT DEFAULT 'pending', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, score INTEGER DEFAULT 0 );