PostgreSQL DEFAULT Constraint

Summary: in this tutorial, you will learn how to use the PostgreSQL DEFAULT constraint to set default values for table columns.

Introduction to PostgreSQL DEFAULT constraint

In PostgreSQL, a table’s column has a default value of NULL. PostgreSQL empowers you to specify a default value for a column with the flexible DEFAULT constraint as follows:

CREATE TABLE table_name(
   column1 data_type DEFAULT default_value,
   column2 data_type,
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, you specify the DEFAULT keyword followed by the column’s default value after the column’s data type:

column1 data_type DEFAULT default_valueCode language: PHP (php)

When you insert a row without providing a value for column1, PostgreSQL will use the default_value for insertion:

INSERT INTO
  table_name (column2)
VALUES
  (value2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can also use the DEFAULT keyword to conveniently represent the default value defined in the column1:

INSERT INTO
  table_name (column1, column2)
VALUES
  (DEFAULT, value2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Basic PostgreSQL DEFAULT constraint example

First, create table items whose tax column has the default value of 5% (or 0.05):

CREATE TABLE items (
  item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  price DEC(11, 2) NOT NULL,
  tax DEC(11, 2) DEFAULT 0.05
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert a new row into the items table without providing a value for the tax column:

INSERT INTO
  items (name, quantity, price)
VALUES
  ('iPhone 15 Pro', 1, 1299.99) RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 item_id |     name      | quantity |  price  | tax
---------+---------------+----------+---------+------
       1 | iPhone 15 Pro |        1 | 1299.99 | 0.05Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL uses the default value 0.05 to insert into the tax column.

Third, insert a new row into the items table and use the DEFAULT keyword for insertion:

INSERT INTO
  items (name, quantity, price, tax)
VALUES
  ('iPhone 16 Pro', 1, 1399.99, DEFAULT) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 item_id |     name      | quantity |  price  | tax
---------+---------------+----------+---------+------
       2 | iPhone 16 Pro |        1 | 1399.99 | 0.05Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since we use the DEFAULT value in the INSERT statement, PostgreSQL uses the default value defined in the tax column for insertion.

Finally, insert a new row into the items table with the value of the tax column:

INSERT INTO
  items (name, quantity, price, tax)
VALUES
  ('iPhone 17 Pro', 1, 1499.99, 0.08) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 item_id |     name      | quantity |  price  | tax
---------+---------------+----------+---------+------
       3 | iPhone 17 Pro |        1 | 1499.99 | 0.08Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL uses the provided value (0.08) instead of the default value in this example.

Setting default values for timestamp columns

In PostgreSQL, the TIMESTAMP data type is used to store date and time values. When you want a TIMESTAMP column to have a default value, you can use the DEFAULT constraint with the CURRENT_TIMESTAMP function. For example:

First, create a new table called orders to store the customer orders:

CREATE TABLE orders (
  order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer VARCHAR(100) NOT NULL,
  ship_to VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The created_at column uses the CURRENT_TIMESTAMP as the default value.

Second, insert a new order into the orders table:

INSERT INTO
  orders (customer, ship_to)
VALUES
  (
    'John Doe',
    '9000 N 1st Street, San Jose, CA 95134'
  ) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 order_id | customer |                ship_to                |         created_at
----------+----------+---------------------------------------+----------------------------
        1 | John Doe | 9000 N 1st Street, San Jose, CA 95134 | 2024-11-22 12:22:22.724668Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we don’t provide a value for the created_at column. Therefore, PostgreSQL inserts using the current timestamp.

Summary

Use the PostgreSQL DEFAULT constraint to set a default value for a column.
Use the DEFAULT keyword to represent the default value for a column with the default constraint.