PostgreSQL | PostgreSQL Basic Syntax | Using Logical Operators (AND, OR, NOT)

Tags:

• PostgreSQL
• Database

PostgreSQL provides three logical operators: AND, OR, and NOT. This page explains how to use logical operators.

For values that represent true or false in PostgreSQL, see “Boolean Data Type”.

Logical AND

AND is logical conjunction. When the left and right operands are TRUE, FALSE, or NULL, the result is as follows.

TRUE   AND  TRUE   -> TRUE
TRUE   AND  FALSE  -> FALSE
TRUE   AND  NULL   -> NULL
FALSE  AND  FALSE  -> FALSE
FALSE  AND  NULL   -> FALSE
NULL   AND  NULL   -> NULL

The entire expression becomes TRUE only when both the left and right operands are TRUE. Also note that TRUE AND NULL becomes NULL, while FALSE AND NULL becomes FALSE.

–

Try it in practice by creating the following test table.

mydb=# create table test(flag1 boolean,  flag2 boolean);
CREATE TABLE
mydb=#

Add the following data to the table.

mydb=# insert into test values (TRUE, TRUE), (TRUE, FALSE), (TRUE, NULL), (FALSE, FALSE), (FALSE, NULL), (NULL, NULL);
INSERT 0 6
mydb=# select * from test;
 flag1 | flag2
-------+-------
 t     | t
 t     | f
 t     |
 f     | f
 f     |
       |
(6 rows)

Then use a SELECT command to check how the result of logical conjunction is determined by the left and right values.

mydb=# select flag1, flag2, flag1 and flag2 as "flag1 and flag2" from test;
 flag1 | flag2 | flag1 and flag2
-------+-------+-----------------
 t     | t     | t
 t     | f     | f
 t     |       |
 f     | f     | f
 f     |       | f
       |       |
(6 rows)

This shows how the result of logical conjunction changes based on the values on the left and right.

Logical OR

OR is logical disjunction. When the left and right operands are TRUE, FALSE, or NULL, the result is as follows.

TRUE   OR  TRUE   -> TRUE
TRUE   OR  FALSE  -> TRUE
TRUE   OR  NULL   -> TRUE
FALSE  OR  FALSE  -> FALSE
FALSE  OR  NULL   -> NULL
NULL   OR  NULL   -> NULL

If at least one of the left and right operands is TRUE, the entire expression is TRUE. Also note that TRUE OR NULL becomes TRUE, while FALSE OR NULL becomes NULL.

–

Try it in practice. Using the same table created for logical conjunction, use a SELECT command to check how logical disjunction is determined by the left and right values.

mydb=# select flag1, flag2, flag1 or flag2 as "flag1 or flag2" from test;
 flag1 | flag2 | flag1 or flag2
-------+-------+----------------
 t     | t     | t
 t     | f     | t
 t     |       | t
 f     | f     | f
 f     |       |
       |       |
(6 rows)

This shows how the result of logical disjunction changes based on the values on the left and right.

Negation (NOT)

The NOT operator is negation. When the right operand is TRUE, FALSE, or NULL, the result is as follows.

NOT  TRUE   -> FALSE
NOT  FALSE  -> TRUE
NOT  NULL   -> NULL

If the right operand is TRUE, the result is FALSE; if the right operand is FALSE, the result is TRUE. NOT NULL becomes NULL.

–

Try it in practice by creating the following test table.

mydb=# create table test2( flag boolean);
CREATE TABLE
mydb=#

Add the following data to the table.

mydb=# insert into test2 values (TRUE),(FALSE),(NULL);
INSERT 0 3
mydb=#

Then use a SELECT command to check how the result of negation is determined by the value on the right.

mydb=# select flag, not flag as "not flag" from test2;
 flag | not flag
------+----------
 t    | f
 f    | t
      |
(3 rows)

This shows how the result of negation changes based on the value on the right.

–

This page explained how to use the logical operators provided by PostgreSQL.