PostgreSQL provides the
IS NULL operator to help test for NULL fields. Since NULL is a special value to represent an empty state, we have special operators to use this value. In this article, we will learn how to use
IS NULL in PostgreSQL.
The basic syntax of BETWEEN is as follows:
[value] IS NULL
This check will return true if the value is NULL and false otherwise.
For our setup, we will use docker compose to create a Postgres database and to connect phpmyadmin. Start by copying the following into a docker compose file called
version: '3' services: db: image: 'postgres:latest' ports: - 5432:5432 environment: POSTGRES_USER: username POSTGRES_PASSWORD: password POSTGRES_DB: default_database volumes: - psqldata:/var/lib/postgresql phpmyadmin: image: phpmyadmin/phpmyadmin links: - db environment: PMA_HOST: db PMA_PORT: 3306 PMA_ARBITRARY: 1 restart: always ports: - 8081:80 volumes: psqldata:
We can run this file, we can use
docker-compose up. One this is done, open up phpmyadmin by going to http://localhost:8081.
You can then login by leaving the host empty and using the following credentials.
POSTGRES_USER: username POSTGRES_PASSWORD: password
In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.
We will be using the sample db provided here: https://dev.Postgres.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.
Next, let's create an
CREATE TABLE actor ( actor_id smallint, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45), last_update TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (actor_id) ) ;
And finally, let's enter a few rows.
INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15 04:34:33'), (2,'NICK','WAHLBERG','2006-02-15 04:34:33'), (3,'ED','CHASE','2006-02-15 04:34:33'), (4,'JENNIFER','DAVIS','2006-02-15 04:34:33'), (5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'), (6,'BETTE','NICHOLSON','2006-02-15 04:34:33'), (7,'GRACE','MOSTEL','2006-02-15 04:34:33'), (8,'MATTHEW', NULL,'2006-02-15 04:34:33')
Often we want to query data to find records with NULL values. This usually indicates an empty state or missing data. Let’s see an example.
select * from actor where last_name is null;
As you can see, we simply use the operator in the
WHERE clause like other operators.
In a similar way, we can combine the IS NULL operator with
select * from actor where last_name is not null;