Defining the DEFAULT value for a column of a new table

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

CREATE TABLE table_name(    column1 type,    column2 type DEFAULT default_value,    column3 type,    ...);

In this syntax, thecolumn2 will receive thedefault_value when youinsert a new row into thetable_name without specifying a value for the column.

If you don’t specify theDEFAULT constraint for the column, its default value isNULL:

CREATE TABLE table_name(    column1type,    column2type,    column3type,    ...);

This often makes sense becauseNULL 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(    column1type,    column2type DEFAULT expression,    column3type,    ...);

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 theINSERT statement and want to use the default value for the insertion, you can use theDEFAULT 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 theALTER TABLE statement:

ALTER TABLE table_nameALTER COLUMN column2SET DEFAULT default_value;

In this syntax:

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

Removing the DEFAULT value from a column

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

ALTER TABLE table_nameALTER COLUMN column2DROP DEFAULT;

In this syntax:

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

PostgreSQL default value examples

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

1) Basic PostgreSQL default value examples

First,create a new table calledproducts to store product data:

CREATE TABLEproducts(   id SERIAL PRIMARY KEY,   name VARCHAR(255) NOTNULL,   price DECIMAL(19,2) NOTNULL DEFAULT0);

Second, insert a row into theproducts 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 theprice column in theINSERT statement; therefore, PostgreSQL uses the default value0.00 for theprice column.

Third, insert one more row into theproducts 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 theDEFAULT keyword as the value for theprice column in theINSERT statement, PostgreSQL uses the default value as0.00 for the column.

Finally, insert a new row into theproducts 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 calledlogs that stores the log messages:

CREATE TABLE logs(   idSERIAL PRIMARY KEY,   message TEXT NOT NULL,   created_atTIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Thecreated_at column uses the current timestamp returned by theCURRENT_TIMESTAMP function as the default value.

Second, insert rows into thelogs 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 theINSERT statement, we don’t specify the value for thecreated_at column, PostgreSQL uses the current timestamp for the insertion.

3) Using DEFAULT constraint with JSONB type

First, create a table calledsettings to store configuration data:

CREATE TABLE settings(   idSERIAL PRIMARY KEY,   name VARCHAR(50)NOT NULL,   configuration JSONBDEFAULT '{}');

Theconfiguration column has theJSONB type with the default value as an empty JSON object.

Second, insert a new row into thesettings table:

INSERT INTO settings(name)VALUES('global')RETURNING*;

Output:

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

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

To remove the default JSONB value from theconfiguration column of thesettings table, you can use the followingALTER TABLE statement:

ALTER TABLE settingsALTER COLUMN configurationDROP DEFAULT;

Summary

• Use theDEFAULT constraint to define a default value for a table column.
• Use theDEFAULT keyword to explicitly use the default value specified in theDEFAULT constraint in theINSERT statement.

Last updated onMarch 15, 2024

On this page

• Defining the DEFAULT value for a column of a new table
• Defining the DEFAULT value for a column of an existing table
• Removing the DEFAULT value from a column
• PostgreSQL default value examples
• Summary