How to Add Default Value in PostgreSQL: Syntax and Examples

In PostgreSQL, you add a default value to a column using the DEFAULT keyword in the CREATE TABLE or ALTER TABLE statement. This sets a value automatically when no explicit value is provided during insert.

Syntax

The DEFAULT keyword sets a default value for a column when no value is specified during data insertion.

You can define it when creating a table or add it later to an existing column.

• CREATE TABLE: Define default value inline with column definition.
• ALTER TABLE: Add or change default value for an existing column.

sql

CREATE TABLE table_name (
  column_name data_type DEFAULT default_value
);

-- Or to add default to existing column:
ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

Example

This example creates a table with a default value for the status column. It also shows how to add a default value to an existing column.

sql

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  product_name TEXT NOT NULL,
  status TEXT DEFAULT 'pending'
);

INSERT INTO orders (product_name) VALUES ('Book');

SELECT * FROM orders;

-- Adding default to existing column
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'processing';

INSERT INTO orders (product_name) VALUES ('Pen');

SELECT * FROM orders;

Output

 id | product_name |  status  
----+--------------+----------
  1 | Book         | pending  
  2 | Pen          | processing
(2 rows)

Common Pitfalls

Common mistakes when adding default values include:

• Forgetting to specify DEFAULT keyword, which causes syntax errors.
• Trying to add a default value to a column that does not exist.
• Assuming existing rows get updated with the new default (they do not).
• Using incompatible data types for the default value.

sql

/* Wrong: Missing DEFAULT keyword */
ALTER TABLE orders
ALTER COLUMN status SET 'shipped';

/* Correct: */
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'shipped';

Quick Reference

Command	Description	Example
CREATE TABLE with DEFAULT	Set default value when creating table	CREATE TABLE t (col INT DEFAULT 0);
ALTER TABLE SET DEFAULT	Add or change default on existing column	ALTER TABLE t ALTER COLUMN col SET DEFAULT 1;
ALTER TABLE DROP DEFAULT	Remove default value from column	ALTER TABLE t ALTER COLUMN col DROP DEFAULT;

Key Takeaways

Use the DEFAULT keyword to set a default value for a column in PostgreSQL.

Defaults can be set during table creation or added later with ALTER TABLE.

Existing rows are not updated when you add or change a default value.

Always ensure the default value matches the column's data type.

Use ALTER TABLE ALTER COLUMN SET DEFAULT to modify defaults on existing columns.