Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql-not-null-constraint/ below:

PostgreSQL - NOT NULL Constraint

Last Updated : 15 Jul, 2025

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.

• 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.

variable_name Data-type NOT NULL;

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

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.

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.

• 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4