PostgreSQL DEFAULT Value

Summary: in this tutorial, you will learn how to assign a default value to a column using the PostgreSQL DEFAULT constraint.

Defining the DEFAULT value for a column of a new table

When creating a table, you can define a default value for a column in the table using the DEFAULT constraint. Here’s the basic syntax:

CREATE TABLE table_name(
    column1 type,
    column2 type DEFAULT default_value,
    column3 type,
    ...
);Code language: SQL (Structured Query Language) (sql)

In this syntax, the column2 will receive the default_value when you insert a new row into the table_name without specifying a value for the column.

If you don’t specify the DEFAULT constraint for the column, its default value is NULL:

CREATE TABLE table_name(
    column1 type,
    column2 type,
    column3 type,
    ...
);Code language: SQL (Structured Query Language) (sql)

This often makes sense because NULL represents unknown data.

The default value can be a literal value such as a number, a string, a JSON object, etc. Additionally, it can be an expression that will be evaluated when the default value is inserted into the table:

CREATE TABLE table_name(
    column1 type,
    column2 type DEFAULT expression,
    column3 type,
    ...
);Code language: SQL (Structured Query Language) (sql)

When inserting a new row into a table, you can ignore the column that has a default value. In this case, PostgreSQL will use the default value for the insertion:

INSERT INTO table_name(column1, colum3)
VALUES(value1, value2);Code language: SQL (Structured Query Language) (sql)

If you specify the column with a default constraint in the INSERT statement and want to use the default value for the insertion, you can use the DEFAULT keyword as follows:

INSERT INTO table_name(column1, column2, colum3)
VALUES(value1,DEFAULT,value2);Code language: SQL (Structured Query Language) (sql)

Defining the DEFAULT value for a column of an existing table

If you want to specify a default value for a column of an existing table, you can use the ALTER TABLE statement:

ALTER TABLE table_name
ALTER COLUMN column2
SET DEFAULT default_value;Code language: PHP (php)

In this syntax:

First, specify the table name in the ALTER TABLE clause (table_name).
Second, provide the name of the column that you want to assign a default value in the ALTER COLUMN clause.
Third, specify a default value for the column in the SET DEFAULT clause.

Removing the DEFAULT value from a column

To drop a default value later, you can also use the ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT statement:

ALTER TABLE table_name
ALTER COLUMN column2
DROP DEFAULT;Code language: PHP (php)

In this syntax:

First, specify the table name in the ALTER TABLE clause.
Second, provide the name of the column that you want to remove the default value in the ALTER COLUMN clause.
Third, use the DROP DEFAULT to remove the default value from the column.

PostgreSQL default value examples

Let’s take some examples of using the DEFAULT constraint to specify a default value for a column.

1) Basic PostgreSQL default value examples

First, create a new table called products to store product data:

CREATE TABLE products(
   id SERIAL PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   price DECIMAL(19,2) NOT NULL DEFAULT 0
);Code language: SQL (Structured Query Language) (sql)

Second, insert a row into the products table:

INSERT INTO products(name)
VALUES('Laptop')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |  name  | price
----+--------+-------
  1 | Laptop |  0.00
(1 row)Code language: SQL (Structured Query Language) (sql)

In this example, we don’t specify a value for the price column in the INSERT statement; therefore, PostgreSQL uses the default value 0.00 for the price column.

Third, insert one more row into the products table:

INSERT INTO products(name, price)
VALUES
   ('Smartphone', DEFAULT)
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |    name    | price
----+------------+-------
  2 | Smartphone |  0.00
(1 row)Code language: SQL (Structured Query Language) (sql)

In this example, we use the DEFAULT keyword as the value for the price column in the INSERT statement, PostgreSQL uses the default value as 0.00 for the column.

Finally, insert a new row into the products table:

INSERT INTO products(name, price)
VALUES
   ('Tablet', 699.99)
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |  name  | price
----+--------+--------
  3 | Tablet | 699.99
(1 row)Code language: SQL (Structured Query Language) (sql)

In this example, we explicitly specify a value for the price column, and PostgreSQL uses the provided value instead of the default value for the insertion.

2) Using DEFAULT constraint with TIMESTAMP columns

First, create a new table called logs that stores the log messages:

CREATE TABLE logs(
   id SERIAL PRIMARY KEY,
   message TEXT NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP   
);Code language: SQL (Structured Query Language) (sql)

The created_at column uses the current timestamp returned by the CURRENT_TIMESTAMP function as the default value.

Second, insert rows into the logs table:

INSERT INTO logs(message)
VALUES('Started the server')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |      message       |         created_at
----+--------------------+----------------------------
  1 | Started the server | 2024-03-15 10:22:48.680802
(1 row)Code language: SQL (Structured Query Language) (sql)

In the INSERT statement, we don’t specify the value for the created_at column, PostgreSQL uses the current timestamp for the insertion.

3) Using DEFAULT constraint with JSONB type

First, create a table called settings to store configuration data:

CREATE TABLE settings(
   id SERIAL PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   configuration JSONB DEFAULT '{}'
);Code language: SQL (Structured Query Language) (sql)

The configuration column has the JSONB type with the default value as an empty JSON object.

Second, insert a new row into the settings table:

INSERT INTO settings(name)
VALUES('global')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |  name  | configuration
----+--------+---------------
  1 | global | {}
(1 row)Code language: SQL (Structured Query Language) (sql)

Since we don’t specify a value for the configuration column, PostgreSQL uses the empty JSON object {} for the insertion.

To remove the default JSONB value from the configuration column of the settings table, you can use the following ALTER TABLE statement:

ALTER TABLE settings 
ALTER COLUMN configuration 
DROP DEFAULT;Code language: PHP (php)

Summary

Use the DEFAULT constraint to define a default value for a table column.
Use the DEFAULT keyword to explicitly use the default value specified in the DEFAULT constraint in the INSERT statement.

Was this tutorial helpful ?