PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
Home / PostgreSQL Tutorial / PostgreSQL Not-Null Constraint

PostgreSQL Not-Null Constraint

Summary: in this tutorial, you will learn about PostgreSQL not-null constraint to ensure that the value of a column is not null.

In database theory, NULL is unknown or missing information. The NULL value is different from empty or zero. For example, we can ask for the email address of a person, if we don’t know, we use the NULL value. In case the person does not have any email address, we can mark it as an empty string.

The NULL value is very special. For example, NULL is not equal to anything even NULL. To check if a value is NULL or not, you use the Boolean operator IS NULL or IS NOT NULL. The expression NULL = NULL returns NULL.

PostgreSQL provides the not-null constraint to enforce a column must not accept NULL values. It means that whenever you insert or update data, you must specify a value that is different from the NULL value.

Add PostgreSQL not-null constraint to columns when creating new table

The following CREATE TABLE statement creates a new table name invoice with not-null constraint.

1
2
3
4
5
6
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)
);

We used NOT NULL followed by the data type of the column to declare the not-null constraint. In this case, it is a column-constraint.

Note that a column can have multiple constraints such as the not-null, check, unique, foreign key appeared next to each other. The order is not important and we are not sure PostgreSQL will check which constraint first. If you use NULL instead of NOT NULL, the column will accept both null and non-null values. If you don’t use both NOT NULL and NULL, PostgreSQL will use NULL by default.

Add PostgreSQL not-null constraint to columns of existing table

To add not-null constraint to a column of an existing table, we use ALTER TABLE statement as follows:

1
2
ALTER  TABLE table_name
ALTER COLUMN column_name SET NOT NULL;

To add not-null constraints to multiple columns of an existing table, we use the following syntax:

1
2
3
ALTER  TABLE table_name
ALTER COLUMN column_name_1 SET NOT NULL,
ALTER COLUMN column_name_2 SET NOT NULL;

Let’s take a look at the following example.

First, we create a new table named production orders ( production_orders):

1
2
3
4
5
6
7
8
CREATE TABLE production_orders (
ID serial PRIMARY KEY,
description VARCHAR (40) NOT NULL,
material_id VARCHAR (16),
qty NUMERIC,
start_date DATE,
finish_date DATE
);

Next, we insert a new row into the production_orders table:

1
2
3
INSERT INTO production_orders (description)
VALUES
('make for infosys inc.');

Then, we want to make sure that the qty field is not null so we will add the not-null constraint to the qty column. However, the table already has data in it. If we try to add the not-null constraint, PostgreSQL will issue an error message.

So we need to update the data first before adding the not-null constraint.

1
2
UPDATE production_orders
SET qty = 1;

We updated the qty column value to 1.

1
2
ALTER TABLE production_orders ALTER COLUMN qty
SET NOT NULL;

After that, we can update the not-null constraints for material_id, start_date, and finish_date columns:

1
2
3
4
UPDATE production_orders
SET material_id = 'ABC',
    start_date = '2015-09-01',
    finish_date = '2015-09-01';

Add not-null constraints to multiple columns:

1
2
3
4
ALTER TABLE production_orders
ALTER COLUMN material_id SET NOT NULL,
ALTER COLUMN start_date SET NOT NULL,
ALTER COLUMN finish_date SET NOT NULL;

Finally, let’s try to break the not-null constraint:

1
2
UPDATE production_orders
SET qty = NULL;

PostgreSQL issued an error message:

1
2
[Err] ERROR:  null value in column "qty" violates not-null constraint
DETAIL:  Failing row contains (1, make for infosys inc., ABC, null, 2015-09-01, 2015-09-01).

The Not-null constraint special case

You can use the check constraint to represent the not-null constraint. So the

1
NOT NULL

is equivalent to

1
CHECK(column IS NOT NULL)

This is useful because sometimes you may want either column a or b is not null, but not both. For example, in the users table, you want either username or email column is not null. You can use the check constraint as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE users (
ID serial PRIMARY KEY,
username VARCHAR (50),
PASSWORD VARCHAR (50),
email VARCHAR (50),
CONSTRAINT username_email_notnull CHECK (
(
username IS NOT NULL
AND email IS NULL
)
OR (
email IS NULL
AND username IS NOT NULL
)
)
);

In this tutorial, we have introduced you to NULL concept and how to use PostgreSQL not-null constraint to make sure that the values in columns are not NULL.

Related Tutorials

  • PostgreSQL CREATE TABLE
  • PostgreSQL Foreign Key
  • PostgreSQL CHECK Constraint
  • PostgreSQL UNIQUE Constraint
  • PostgreSQL Primary Key
Previous Tutorial: The Basics Of PostgreSQL UUID Data Type
Next Tutorial: PostgreSQL CREATE DATABASE

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete

Managing Table Structure

  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

PostgreSQL Views

  • Managing PostgreSQL Views
  • Creating Updatable Views
  • PostgreSQL Materialized Views
  • The WITH CHECK OPTION Views
  • PostgreSQL Recursive View

PostgreSQL Triggers

  • Introduction to Trigger
  • Creating A Trigger
  • Managing PostgreSQL Triggers

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • How To Change The Password of a PostgreSQL User
  • PostgreSQL AGE Function
  • PostgreSQL DATE_PART Function
  • PostgreSQL List Users
  • PostgreSQL NOW Function
  • PostgreSQL DATE_TRUNC Function
  • PostgreSQL TO_DATE Function: Convert String to Date
  • A Look at PostgreSQL User-defined Data Types
  • PostgreSQL Copy Database Made Easy
  • How to Get Table, Database, Indexes, Tablespace, and Value Size in PostgreSQL

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.