PostgreSQL – NOT NULL Constraint

In PostgreSQL, the NOT NULL constraint is a fundamental feature to ensure that a column cannot contain NULL values. NULL represents unknown or missing information in databases, distinct from an empty string or the number zero. For example, if you ask someone for their email address and they don’t know it, you would insert NULL into the email column. This indicates that the data is unknown at the time of entry. Conversely, if the person has no email address, you might use an empty string instead.

Let us better understand the NOT NULL Constraint in PostgreSQL from this article.

Key Characteristics of NULL in PostgreSQL

• Uniqueness: NULL is unique in that NULL is not equal to any other NULL. The expression NULL = NULL returns NULL, not true.
• Checking for NULL: To check if a value is NULL, use the Boolean operators IS NULL or IS NOT NULL.

Syntax

variable_name Data-type NOT NULL;

PostgreSQL – NOT NULL Constraint Example

Now let’s look into an example to better understand the concept of NOT NULL.

Step 1: Creating a Table with NOT NULL Constraints

We will create a table named ‘invoice' with various constraints, including NOT NULL:

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

In this table:

• 'product_id' and ‘qty' columns have the NOT NULL constraint, meaning they cannot contain NULL values.
• 'qty' and ‘net_price' columns also have CHECK constraints to ensure they are greater than zero.

Step 2: Inserting Valid Data

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

Step 3: Verifying Data Insertion

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:

Step 4: Inserting Invalid Data

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.

Important Points About NOT NULL Constraint in PostgreSQL

• The NOT NULL constraint is used to ensure that a column cannot contain NULL values, enforcing the presence of a value in every row of the column.
• Use the Boolean operators ‘IS NULL' or ‘IS NOT NULL' to check if a value is NULL in queries. This is different from the NOT NULL constraint which prevents NULL values from being entered in the first place.
• NOT NULL constraints are typically used for columns that must always have a value, such as primary keys, foreign keys, and essential business data fields like email addresses, usernames, and quantities.
• NOT NULL is often combined with other constraints like UNIQUE, CHECK, and PRIMARY KEY to provide comprehensive data validation and integrity rules.

R

RajuKumar19

Follow

Improve

Article Tags :

• PostgreSQL
• postgreSQL-constraints