Skip to content

Filtering Data

To filter the data from the table, we will use WHERE condition. This condition will evaluate and return true or false. The query returns only rows that satisfy the condition in the WHERE clause.

SELECT <column name> FROM <table name> WHERE <condition>

SELECT first_name FROM actor WHERE first_name='Tim';

pg_16

If you are using the column aliases, you can't use them in the query.

SELECT first_name AS "First name" FROM actor WHERE 'First name'='Tim'; -> This will throw an error.

To form the condition in the WHERE clause, you use comparison and logical operators

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> or != Not equal
AND Logical operator AND
OR Logical operator OR
IN Return true if a value matches any value in a list
BETWEEN Return true if a value is between a range of values (range inclusive)
LIKE Return true if a value matches a pattern
IS NULL Return true if a value is NULL
NOT Negate the result of other operators

WHERE clause with the equal (=) operator

SELECT first_name FROM actor WHERE first_name='Tim';

pg_16

WHERE clause with the AND operator

SELECT first_name FROM actor WHERE first_name='Tim' AND last_name='Hackman';

pg_17

WHERE clause with the OR operator

SELECT first_name, last_name FROM actor WHERE first_name='Tim' OR last_name='Hackman';

pg_18

WHERE clause with the IN operator

Remember that when the column involves date with timestamp, you have to give column::date in the filter condition

SELECT first_name, last_name FROM actor WHERE first_name in ('Dan','Bob','Sandra','Elvis');

Same query can be written like below 

SELECT first_name, last_name FROM actor WHERE first_name='Dan' OR first_name='Bob' OR first_name='Sandra' OR first_name='Elvis';

pg_19

WHERE clause with the LIKE operator

SELECT first_name, last_name FROM actor WHERE first_name LIKE 'An%';

% will match zero or more characters.

pg_20

SELECT first_name, last_name FROM actor WHERE first_name LIKE 'An_e';
_ will match only one character.

pg_21

WHERE clause with the BETWEEN operator

BETWEEN includes the range boundary as well. It means that between 3 and 5 includes 3 and 5 value too.

SELECT
  first_name,
  LENGTH(first_name) "NAME LENGTH"
FROM
  actor
WHERE
  first_name LIKE 'A%'
  AND LENGTH(first_name) BETWEEN 4
  AND 6
ORDER BY
  "NAME LENGTH";

pg_22

WHERE clause with the not equal operator

SELECT
  first_name,
  last_name
FROM
  actor
WHERE
  first_name LIKE 'Ang%'
  AND last_name <> 'Hudson';

pg_23

PostgresSQL AND operator

First Condition Second Condition Result
true true true
true false false
true null null
false false false
false true false
false null false

Postgres OR Operator

First Condition Second Condition Result
true true true
true false true
true null true
false false false
false true true
false null null

LIMIT

LIMIT will filter the number of rows to be displayed from the results returned by query. It is an optional clause.

SELECT <column name>
FROM <table name>
ORDER BY <sort expression>
LIMIT <row count>;

SELECT first_name, last_name FROM actor LIMIT 5;

pg_24

OFFSET

OFFSET will skip the number of rows returned from the results returned by query. It is an optional clause.

SELECT <column name>
FROM <table name>
ORDER BY <sort expression>
LIMIT <row count>
OFFSET <row count to be skipped>;

SELECT actor_id, first_name, last_name FROM actor LIMIT 5 OFFSET 5;

pg_25

Order of execution in the above query is below

  1. Query execution
  2. Offset implementation
  3. Limit the result after offset implementation

LIMIT OFFSET to get top/bottom N rows

TOP rows

SELECT actor_id, first_name, last_name FROM actor ORDER BY actor_id LIMIT 7 OFFSET 3;
pg_26

BOTTOM rows

SELECT actor_id, first_name, last_name FROM actor ORDER BY actor_id DESC LIMIT 7 OFFSET 3;

pg_27

FETCH

This is similar to LIMIT.

SELECT <column name>
FROM <table name>
ORDER BY <sort expression>
OFFSET <row to skip> { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

SELECT actor_id, first_name, last_name FROM actor OFFSET 5 FETCH NEXT 3 ROWS ONLY;

or 

SELECT actor_id, first_name, last_name FROM actor OFFSET 5 FETCH FIRST 3 ROWS ONLY;

pg_28

PostgresSQL IN operator with a list of dates

SELECT first_name, last_name FROM actor WHERE first_name IN ('Tim', 'Anne');

pg_30

BETWEEN

SELECT rental_date FROM rental WHERE rental_date::date BETWEEN '2005-05-25' AND '2005-05-26' LIMIT 5;

pg_29

PostgresSQL LIKE operator

Suppose that you want to find customers, but you don’t remember their names exactly. However, you can recall that their names begin with something like Jen.

You can use the PostgresSQL LIKE operator to match the first names of customers with a string using the following query.

SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name LIKE 'Jen%';

pg_31

PostgresSQL ILIKE operator

ILIKE is similar to LIKE, but it is case-insensitive. 

SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name ILIKE 'jEn%';

pg_32

Operator Equivalent
~~ LIKE
~~* ILIKE
!~~ NOT LIKE
!~~* NOT ILIKE

pg_33

pg_34

SIMILAR TO

SIMILAR TO operator in PostgresSQL offers a way to search for patterns that follow regular expression rules. The SIMILAR TO operator allows you to match a pattern to a string using regular expression syntax

value SIMILAR TO pattern
Where:

value is the string or column to be matched.

pattern is the regular expression pattern that you want to compare against.

Basic SIMILAR TO

Below query fetches the first name starts with 'J' and followed by any one of the characters mentioned within the bracket and followed by 'n' and any characters.

SELECT first_name, last_name
FROM customer
WHERE first_name SIMILAR TO 'J(e|o|a|u|i)n%';

pg_35

SIMILAR TO with Multiple Patterns

Below query fetches the first name starts with either "J" or "M" and followed by any 3 characters.

select first_name from actor where first_name SIMILAR TO '[JM]___' LIMIT 5

pg_36

SIMILAR TO with Wildcards

Below, a query fetches the first name contains 'l'. Prefix and suffix of 'l' can be anything.

select first_name from actor where first_name SIMILAR TO '%l%' LIMIT 7;

pg_37

ESCAPE

ESCAPE clause in SQL is used to define an escape character in a query. This allows you to escape special characters (like %, _, ', and others) that are typically used for pattern matching or other special functions.

Single Quote (')

select title from test where title SIMILAR TO '%''%';

pg_38

Percent Sign (%)

If you want to match a literal %, you need to escape it.

select * from test where title like '%$%%' escape '$';

pg_39

Underscore(_)

To use an underscore literally (i.e., if you’re searching for an actual underscore in your data), you need to escape it.

select first_name from actor where first_name LIKE '!T_%' escape '!';

pg_40

Character Escape Required Example
Single Quote (') Yes 'It''s a movie'
Percent Sign (%) Yes (with ESCAPE) LIKE '%!%%' ESCAPE '!'
Underscore (_) Yes (with ESCAPE) LIKE '%!_%' ESCAPE '!'
Backslash () Yes 'This is a backslash: \'
Square Brackets ([]) Yes (with ESCAPE) LIKE '%[[]%' ESCAPE '!'