Summary: in this tutorial, you will learn how to assign a default value to a column using the PostgreSQL DEFAULT constraint.
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);
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:
ALTER TABLE
clause (table_name
).ALTER COLUMN
clause.SET DEFAULT
clause.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:
ALTER TABLE
clause.ALTER COLUMN
clause.DROP DEFAULT
to remove the default value from the column.Let’s take some examples of using the DEFAULT
constraint to specify a default value for a column.
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 columnsFirst, 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.
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;
DEFAULT
constraint to define a default value for a table column.DEFAULT
keyword to explicitly use the default value specified in the DEFAULT
constraint in the INSERT
statement.RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4