{"id":248,"date":"2024-11-22T22:40:00","date_gmt":"2024-11-22T15:40:00","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=248"},"modified":"2025-01-02T07:57:33","modified_gmt":"2025-01-02T00:57:33","slug":"postgresql-default-constraint","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-default-constraint\/","title":{"rendered":"PostgreSQL DEFAULT Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the PostgreSQL DEFAULT<\/code> constraint to set default values for table columns.<\/p>\n\n\n\n

Introduction to PostgreSQL DEFAULT constraint #<\/a><\/h2>\n\n\n\n

In PostgreSQL, a table’s column has a default value of NULL<\/code><\/a>. PostgreSQL empowers you to specify a default value for a column with the flexible DEFAULT<\/code> constraint as follows:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name<\/span>(\n   column1 data_type DEFAULT<\/span> default_value,\n   column2 data_type,\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the DEFAULT<\/code> keyword followed by the column’s default value after the column’s data type:<\/p>\n\n\n
column1 data_type DEFAULT<\/span> default_value<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span>\n  table_name<\/span> (column2)\nVALUES<\/span>\n  (value2);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can also use the DEFAULT<\/code> keyword to conveniently represent the default value defined in the column1<\/code>:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  table_name<\/span> (column1, column2)\nVALUES<\/span>\n  (DEFAULT<\/span>, value2);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Basic PostgreSQL DEFAULT constraint example #<\/a><\/h2>\n\n\n\n
First, create table<\/a> items<\/code> whose tax<\/code> column has the default value of 5% (or 0.05<\/code>):<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> items (\n  item_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  quantity INT<\/span> NOT<\/span> NULL<\/span>,\n  price DEC<\/span>(11<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n  tax DEC<\/span>(11<\/span>, 2<\/span>) DEFAULT<\/span> 0.05<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span>\n  items (name<\/span>, quantity, price)\nVALUES<\/span>\n  ('iPhone 15 Pro'<\/span>, 1<\/span>, 1299.99<\/span>) RETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 item_id |     name<\/span>      | quantity |  price  | tax\n---------+---------------+----------+---------+------<\/span>\n       1<\/span> | iPhone 15<\/span> Pro |        1<\/span> | 1299.99<\/span> | 0.05<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL uses the default value 0.05<\/code> to insert into the tax<\/code> column.<\/p>\n\n\n\n
Third, insert a new row into the items<\/code> table and use the DEFAULT<\/code> keyword for insertion:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  items (name<\/span>, quantity, price, tax)\nVALUES<\/span>\n  ('iPhone 16 Pro'<\/span>, 1<\/span>, 1399.99<\/span>, DEFAULT<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 item_id |     name      | quantity |  price  | tax\n---------+---------------+----------+---------+------\n       2 | iPhone 16 Pro |        1 | 1399.99 | 0.05<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Since we use the DEFAULT<\/code> value in the INSERT<\/code> statement, PostgreSQL uses the default value defined in the tax<\/code> column for insertion.<\/p>\n\n\n\n
Finally, insert a new row into the items<\/code> table with the value of the tax<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  items (name<\/span>, quantity, price, tax)\nVALUES<\/span>\n  ('iPhone 17 Pro'<\/span>, 1<\/span>, 1499.99<\/span>, 0.08<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 item_id |     name<\/span>      | quantity |  price  | tax\n---------+---------------+----------+---------+------<\/span>\n       3<\/span> | iPhone 17<\/span> Pro |        1<\/span> | 1499.99<\/span> | 0.08<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL uses the provided value (0.08<\/code>) instead of the default value in this example.<\/p>\n\n\n\n
Setting default values for timestamp columns #<\/a><\/h2>\n\n\n\n
In PostgreSQL, the TIMESTAMP<\/code> data type is used to store date and time values. When you want a TIMESTAMP<\/code> column to have a default value, you can use the DEFAULT<\/code> constraint with the CURRENT_TIMESTAMP<\/code> function. For example:<\/p>\n\n\n\n
First, create a new table called orders<\/code> to store the customer orders:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> orders (\n  order_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  customer VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n  ship_to VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  created_at TIMESTAMP<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The created_at<\/code> column uses the CURRENT_TIMESTAMP<\/code> as the default value.<\/p>\n\n\n\n
Second, insert a new order into the orders<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  orders (customer, ship_to)\nVALUES<\/span>\n  (\n    'John Doe'<\/span>,\n    '9000 N 1st Street, San Jose, CA 95134'<\/span>\n  ) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 order_id | customer |                ship_to                |         created_at\n----------+----------+---------------------------------------+----------------------------\n        1 | John Doe | 9000 N 1st Street, San Jose, CA 95134 | 2024-11-22 12:22:22.724668<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we don’t provide a value for the created_at<\/code> column. Therefore, PostgreSQL inserts using the current timestamp.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Second, insert a new row<\/a> into the items<\/code> table without providing a value for the tax<\/code> column:<\/p>\n\n\n
When you insert a row<\/a> without providing a value for column1<\/code>, PostgreSQL will use the default_value<\/code> for insertion:<\/p>\n\n\n