{"id":193,"date":"2024-11-21T09:14:35","date_gmt":"2024-11-21T02:14:35","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=193"},"modified":"2025-01-22T09:55:00","modified_gmt":"2025-01-22T02:55:00","slug":"postgresql-not-null","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-not-null\/","title":{"rendered":"PostgreSQL NOT NULL Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn about NULL<\/code> and how to use the PostgreSQL NOT NULL<\/code> constraint to ensure a column will not have NULL<\/code>.<\/p>\n\n\n\n

Introduction to NULL #<\/a><\/h2>\n\n\n\n

NULL<\/code> represents the absence of a value, indicating that the data is unknown or missing.<\/p>\n\n\n\n

For example, if you want to record warehouse information but don’t know its address at the time of recording, you can put NULL<\/code> into the address<\/code> column. Later, you can update<\/a> NULL<\/code> in the address<\/code> column with the correct address.<\/p>\n\n\n\n

Note that NULL<\/code> means an unknown value. It is different from an empty string (''<\/code>) or zero (0), which are the defined values.<\/p>\n\n\n\n

PostgreSQL uses three-valued logic<\/em> for NULL<\/code>. This means that if you compare NULL<\/code> with any value, the comparison returns NULL<\/code>.<\/p>\n\n\n\n

For example, comparing NULL<\/code> with a number will result in NULL<\/code>:<\/p>\n\n\n

SELECT<\/span>\n  NULL<\/span> = 1<\/span> AS<\/span> result;<\/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
 result\n--------\n NULL<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Even NULL<\/code> is not equal to NULL<\/code>; comparing NULL<\/code> with NULL<\/code> also results in NULL<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  NULL<\/span> = NULL<\/span> AS<\/span> result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
This result makes sense because comparing an unknown value with another value will result in an unknown result.<\/p>\n\n\n\n
Inserting NULL into a column #<\/a><\/h2>\n\n\n\n
When you define a column for a table, that column is nullable<\/em>. It means that you can insert<\/a> NULL<\/code> into the column.<\/p>\n\n\n\n
For example, the following creates a table<\/a> called warehouses<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> warehouses (\n  warehouse_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(255<\/span>),\n  address VARCHAR<\/span>(255<\/span>)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The data types of name<\/code> and address<\/code> columns are VARCHAR<\/code>, but they can accept NULL<\/code>.<\/p>\n\n\n\n
The following INSERT<\/code> statement inserts a new row into the warehouses<\/code> table with NULL<\/code> in the address<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  warehouses (name<\/span>, address)\nVALUES<\/span>\n  ('San Jose'<\/span>, NULL<\/span>)\nRETURNING<\/span>\n  name<\/span>,\n  address;<\/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
   name   | address\n----------+---------\n San Jose | NULL<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
If you omit a column in the INSERT<\/code> statement, PostgreSQL will use NULL<\/code> as the default value. For example:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  warehouses (name<\/span>)\nVALUES<\/span>\n  ('San Francisco'<\/span>)\nRETURNING<\/span>\n  name<\/span>,\n  address;<\/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
     name      | address\n---------------+---------\n San Francisco | NULL<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Using PostgreSQL NOT NULL constraint #<\/a><\/h2>\n\n\n\n
To ensure that a column cannot have NULL<\/code>, you use the NOT NULL<\/code> constraint.<\/p>\n\n\n\n
Here’s the syntax for defining a NOT NULL<\/code> constraint for a column:<\/p>\n\n\n
column_name<\/span> data_type NOT<\/span> NULL<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
After defining a NOT NULL<\/code> constraint, the column will not accept NULL. If you attempt to do so, PostgreSQL will issue an error.<\/p>\n\n\n\n
The NOT NULL<\/code> constraint is crucial for maintaining data integrity and consistency across your database.<\/p>\n\n\n\n
By enforcing a NOT NULL<\/code> constraint, you can ensure that essential columns such as email addresses always have value. The NOT NULL<\/code> constraints prevent situations where NULL could lead to errors in data processing.<\/p>\n\n\n\n
The NOT NULL<\/code> constraint simplifies data management because you won’t need to write additional logic to handle NULL<\/code>, making your SQL statements simpler and more efficient.<\/p>\n\n\n\n
The NOT NULL<\/code> constraint also helps enforce business rules at the database level, ensuring your app does not operate with unknown values.<\/p>\n\n\n\n
PostgreSQL NOT NULL constraint example #<\/a><\/h2>\n\n\n\n
First, drop the warehouses<\/code> table:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> warehouses;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, recreate the warehouses<\/code> table whose name<\/code> and address<\/code> columns have the NOT NULL<\/code> constraints:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> warehouses (\n  warehouse_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  address VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, attempt to insert NULL<\/code> into the address<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  warehouses (name<\/span>, address)\nVALUES<\/span>\n  ('San Meto'<\/span>, NULL<\/span>)\nRETURNING<\/span>\n  name<\/span>,\n  address;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Error:<\/p>\n\n\n
ERROR: null value in column \"address\" of relation \"warehouses\" violates not-null constraint<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL issued a not-null constraint violation and rejected the insert.<\/p>\n\n\n\n
Finally, insert a new row into the warehouses<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  warehouses (name<\/span>, address)\nVALUES<\/span>\n  (\n    'San Meto'<\/span>,\n    ' 1st Ave Suite K03, San Mateo, CA 94403'<\/span>\n  )\nRETURNING<\/span>\n  name<\/span>,\n  address;<\/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
   name   |                 address\n----------+-----------------------------------------\n San Meto |  1st Ave Suite K03, San Mateo, CA 94403<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\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
Note that primary key<\/a> columns implicitly have a NOT NULL<\/code> constraint.<\/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
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n