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)

Try it

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)

Try it

Output:

 item_id |     name      | quantity |  price  | tax
---------+---------------+----------+---------+------
       2 | iPhone 16 Pro |        1 | 1399.99 | 0.05Code language: plaintext (plaintext)

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)

Try it

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)

Try it

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: plaintext (plaintext)

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.

Quiz #