How to use logical operators in PostgreSQL

Linux x86-64 (RHEL 8)

Tushar Ahuja Sr. QA Manager

SUMMARY: This article looks at logical operators in PostgreSQL and how to use them. The logical operators in PostgreSQL are AND, OR, and NOT.

1. AND

            a. AND operator truth table

            b. Examples

2. OR 

            a. OR operator truth table

            b. Examples

3. NOT

            a. NOT operator truth table

            b. Examples

4. Using AND and OR operators together

 

Logical operators (also known as boolean operators)  are used to estimate some set of  conditions where the result can be TRUE, FALSE, or NULL (missing, unknown, unavailable, unassigned).

There are 3 logical operators available in PostgreSQL: 

AND

OR

NOT 

These boolean operators are used to match conditions in a SQL statement—e.g., in WHERE  and HAVING clauses.

AND = if both boolean expressions are true then it will return TRUE

OR   =  if any boolean expression is true then it will return TRUE

NOT = Reverses the value of Boolean operator.

 

AND

AND operator truth table

x1        

y1

x1 AND y1

TRUE

TRUE

TRUE

TRUE

FALSE

FALSE

TRUE

NULL

NULL

FALSE

FALSE

FALSE

FALSE

NULL

FALSE

NULL

NULL

NULL

 

Examples

  • Both expressions are true, so the result is TRUE.
postgres=# select 1=1 and 2=2;

 ?column? 

 ----------

   t

(1 row)

 

  •  One expression is true and another expression is false, so the result is FALSE.
           postgres=# select 1=1 and 2=3;

 ?column? 

 ----------

    f

(1 row)

 

  • One expression is true and another expression is NULL, so the result is NULL.
postgres=# select 1=1 and null;

 ?column? 

----------



(1 row)

 

  •  Both expressions are false, so the result is FALSE. 
 postgres=# select 1=3 and 2=3;

 ?column? 

 ----------

 f

(1 row)

 

  • One expression is false and another expression is NULL, so the result is FALSE.
postgres=# select 1=3 and null;

 ?column? 

----------

    f

(1 row)

 

  • Both expressions are NULL so the result is NULL.
postgres=# select null and null;

 ?column? 

----------



(1 row)

 

OR

OR operator truth table

x1

y1

x1 OR  y1

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

TRUE

NULL

TRUE

FALSE

FALSE

FALSE

FALSE

NULL

NULL

NULL

NULL

NULL

 

Examples

  • Both expressions are true, so the result is TRUE.
  postgres=# select 1=1 OR 2=2;

             ?column? 

 ----------

               t

            (1 row)

 

  •  One expression is true  and another expression is false so the result is TRUE.
postgres=# select 1=1 OR 2=3;

 ?column? 

----------

   t

(1 row)

 

  • One expression is true and another expression is NULL, so the result is TRUE.
postgres=# select 1=1 OR null;

  ?column? 

 ----------

    t

(1 row)

 

  •  Both expressions are false, so the result is FALSE.
 postgres=# select 1=3 OR 2=3;

   ?column? 

   ----------

      f

 (1 row)

 

  • One expression is false and another expression is NULL so the result is NULL.
 postgres=# select 1=3 OR null;

   ?column? 

    ----------



   (1 row)

 

  • Both expressions are NULL so the result is NULL.
postgres=# select null OR null;

   ?column? 

   ----------



  (1 row)

 

NOT 

NOT operator truth table

x1

NOT x1

TRUE

FALSE

FALSE

TRUE

NULL

NULL

 

Examples

  • Expression is NOT true so the result is FALSE. 
   postgres=# select not true;

            ?column? 

             ----------

               f

            (1 row)

 

  •  Expression is NOT false so the result is TRUE.
postgres=# select not false;

 ?column? 

----------

  t

          (1 row)

 

  •  Expression is NOT NULL so the result is NULL. 
postgres=# select not null;

 ?column? 

 ----------



(1 row)

 

Using AND and OR operators together 

We can combine the AND and OR operators in a SQL query to perform INSERT, UPDATE, SELECT, and DELETE operations. 

Remember to include parentheses () so that the server knows in which order to evaluate the query.

Examples

  • Both expressions return TRUE, so the result will be TRUE. 
postgres=# select (5=5 and 5=5) and ( null or 4=4);

 ?column? 

 ----------

              t

           (1 row)

 

  • One expression returns TRUE and another returns NULL, so the result is NULL.
postgres=# select (5=5 and 5=5) and ( null and 4=4);

 ?column? 

 ----------



(1 row)

 

Please refer to the PostgreSQL documentation for more details: 

https://www.postgresql.org/docs/11/functions-logical.html.

Hope it helps! 

 

 

 

Tushar AhujaSr. QA Manager