PostgreSQL – NOT NULL Constraint
PostgreSQL Not-Null constraint as the name suggests is used to ensure that any value in the respective column is not null. In the world of database, NULL is unknown or missing information. The NULL value is separate from an empty string or the number zero. For instance, one can ask a person for an email address, if you don’t know, you use the NULL value for inserting it into the email column. This suggests that the data at the time of inserting is unknown. In case the person does not have an email address, one can update it to an empty string.
The NULL value is remarkably special. For instance, NULL is not equal to any other NULL. To check if a value is NULL or not, one can use the Boolean operator
IS NULL or
IS NOT NULL. The expression NULL = NULL returns NULL.
PostgreSQL provides the not-null constraint to implement conditions that a column must not accept NULL values. It means that whenever you insert or update data, you must specify a value that is not null.
Syntax: variable_name Data-type NOT NULL;
Now let’s look into an example to better understand the concept of NOT NULL.
First, we will create a table
invoice with NOT NULL constraint as follows:
CREATE TABLE invoice( id serial PRIMARY KEY, product_id int NOT NULL, qty numeric NOT NULL CHECK(qty > 0), net_price numeric CHECK(net_price > 0) );
At this stage we will first insert data that satisfies the above constraint as follows:
INSERT INTO invoice (product_id, qty, net_price) VALUES ('1', '5', 255);
Now we will check if the data has been successfully inserted using the below command:
SELECT * FROM invoice;
This will result in the below output:
Now we will try to insert a NULL value to the invoice table as below:
INSERT INTO invoice (product_id, qty, net_price) VALUES ('1', NULL, 255);
ERROR: null value in column "qty" violates not-null constraint DETAIL: Failing row contains (2, 1, null, 255).
We can observe the NOT NULL constraint behaves as expected.