Postgresql Where Clause

PostgreSQL WHERE Clause |

In PostgreSQL, when we need to query data from a single or multiple tables based on specified conditions, we can add a WHERE clause to the SELECT statement to filter out the data we don't need.

The WHERE clause can be used not only in SELECT statements, but also in UPDATE, DELETE, and other statements.

Syntax

The following is the general syntax for using the WHERE clause in a SELECT statement to read data from a database:

SELECT column1, column2, columnN FROM table_name WHERE

We can use comparison operators or logical operators in the WHERE clause, such as >, <, =, LIKE, NOT, etc.

Create a COMPANY table (Download COMPANY SQL file), the data content is as follows:

tutorialdb# select * from COMPANY;
 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(7 rows)

The following examples use logical operators to read data from the table.

AND

Find data where the AGE field is greater than or equal to 25, and the SALARY field is greater than or equal to 65000:

tutorialdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(2 rows)

OR

Find data where the AGE field is greater than or equal to 25, or the SALARY field is greater than or equal to 65000:

tutorialdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(4 rows)

NOT NULL

Find records where the AGE field is not null in the company table:

tutorialdb=# SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(7 rows)

LIKE

Find data in the COMPANY table where the NAME field starts with 'Pa':

tutorialdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
 id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000
(1 row)

IN

The following SELECT statement lists data where the AGE field is 25 or 27:

tutorialdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  2 | Allen |  25 | Texas     |  15000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(3 rows)

NOT IN

The following SELECT statement lists data where the AGE field is not 25 or 27:

tutorialdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000

YouTip