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,  ... );

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,  ... );

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,  ... );

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);

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);

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;

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;

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 );

Second, insert a row into the products table:

INSERT INTO products(name) VALUES('Laptop') RETURNING *;

Output:

id | name | price ----+--------+-------  1 | Laptop | 0.00 (1 row)

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 *;

Output:

id | name | price ----+------------+-------  2 | Smartphone | 0.00 (1 row)

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 *;

Output:

id | name | price ----+--------+--------  3 | Tablet | 699.99 (1 row)

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 );

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 *;

Output:

id | message | created_at ----+--------------------+----------------------------  1 | Started the server | 2024-03-15 10:22:48.680802 (1 row)

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 '{}' );

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 *;

Output:

id | name | configuration ----+--------+---------------  1 | global | {} (1 row)

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;

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.