Open In App

PostgreSQL – NOT NULL Constraint

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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.

Example:
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);

Output:

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.


Last Updated : 01 Jun, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads