PostgreSQL NOT NULL Constraint

In PostgreSQL, NOT NULL is a constraint on a column, which is used to constrain that the value in the column cannot be a NULL value.

Note that NULL is not a empty string, nor is 0, it means nothing. You can use the IS NULL operator to check if a value is NULL.

PostgreSQL NOT NULL syntax

To define a NOT NULL column, use the following methods.

Define NOT NULL columns

Use the following syntax to define a NOT NULL column:

CREATE TABLE table_name (
  ...
  column_name data_type NOT NULL ...,
  ...
);

Add NOT NULL constraint

To add a NOT NULL constraint to an existing column, modify the definition of the column using the following ALTER TABLE syntax:

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;

The column_name is the name of the column to add the NOT NULL constraint.

Remove NOT NULL constraint

To drop a NOT NULL constraint, modify the definition of the column using the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name DROP NOT NULL;

The column_name is the name of the column from which to drop the NOT NULL constraint.

PostgreSQL NOT NULL Examples

Let’s understand the usages of NOT NULL by a example. Please follow the steps below:

1. Create a user_hobby table:

   DROP TABLE IF EXISTS user_hobby;
   CREATE TABLE user_hobby (
     hobby_id SERIAL PRIMARY KEY,
     user_id INTEGER NOT NULL,
     hobby VARCHAR(45) NOT NULL
   );
   

   Here, the user_id and hobby columns ​does not accept NULL values.

2. Insert several rows for demonstration:

   INSERT INTO user_hobby (user_id, hobby)
   VALUES (1, 'Football'), (1, 'Swimming');
   

   Obviously they can be inserted successfully.

3. Insert a null value into hobby column :

   INSERT INTO user_hobby (user_id, hobby)
   VALUES (1, NULL);
   

   ERROR:  null value in column "hobby" violates not-null constraint
   DETAIL:  Failing row contains (3, 1, null).

   The PostgreSQL server returned the above error. Because the hobby column cannot be empty.

Modify an existing column to be non-nullable

If you want to modify an existing column that allows NULL values ​​to not allow NULL values, please modify the NULL value non-null value first, otherwise you may encounter errors.

Suppose, we have the following table:

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE user_hobby (
  hobby_id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45)
);

Now, the hobby columns can accept NULL values.

Let’s insert a few rows of test data:

INSERT INTO user_hobby (user_id, hobby)
VALUES (1, 'Football'), (1, NULL);

Now let’s query the rows in the following table:

SELECT * FROM user_hobby;

hobby_id | user_id |  hobby
----------+---------+----------
       1 |       1 | Football
       2 |       1 |
(2 rows)

Run the following statement to add NOT NULL constraints to the hobby column:

ALTER TABLE user_hobby
ALTER hobby SET NOT NULL;

ERROR:  column "hobby" contains null values

PostgreSQL returned the above error. This is because of there is a null value in the hobby column.

Let’s change the null value ​​in the hobby column to a non-null value:

UPDATE user_hobby
SET hobby = 'NOTHING'
WHERE hobby IS NULL;

Then, let’s add NOT NULL constraints to the hobby columns:

ALTER TABLE user_hobby
ALTER hobby SET NOT NULL;

The NOT NULL constraint has been added to the hobby column now.

Conclusion

In this article, we learned how to usages of NOT NULL constraints.