Filtering Data

Where

select select_list from table_name where condition order by sort_expression;

Postgres will evaluate where after from but before order by.

We can also use where in update and delete statement

Note in the where cause, we can't use column alias

SELECT first_name, last_name FROM customer WHERE first_name IN ('Ann', 'Anne', 'Annie');

For example with like

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

The % is a wildcard pattern match all

For example with between to select customer where name start with A and has 3-5 letter in first name;

SELECT first_name FROM customer WHERE first_name LIKE 'A%' AND LENGTH(first_name) BETWEEN 3 AND 5 ORDER BY name_length;

AND operator

Mostly the logic is the same, some special case:

• True AND Null → Null
• False AND Null → False
• Null and Null → Null

A way to test:

select True and Null result;

Or Operation

Some special cases

• False Or Null → Null
• True Or Null → True

Limit

Limit the number of output rows

select * from customer where first_name like 'Am%' limit 1;

This would return the first matches customer. Normally, limit is used combined with order by when we want to find top N element.

Offset

Offset specified the number of row to skip. For example:

postgres@localhost:dvdrental> select first_name, last_name from customer where first_name like 'Am%';
+------------+-----------+
| first_name | last_name |
|------------+-----------|
| Amy        | Lopez     |
| Amanda     | Carter    |
| Amber      | Dixon     |
+------------+-----------+
SELECT 3
Time: 0.007s
postgres@localhost:dvdrental> select first_name, last_name from customer where first_name like 'Am%' offset 1;
+------------+-----------+
| first_name | last_name |
|------------+-----------|
| Amanda     | Carter    |
| Amber      | Dixon     |
+------------+-----------+
SELECT 2
Time: 0.007s

offset will skip the first row. We can use in conjunction with limit.

For example, query 4 movie but taking from the 4th one:

postgres@localhost:dvdrental> select film_id, title, release_year from film order by film_id limit 4 offset 3;
+---------+------------------+--------------+
| film_id | title            | release_year |
|---------+------------------+--------------|
| 4       | Affair Prejudice | 2006         |
| 5       | African Egg      | 2006         |
| 6       | Agent Truman     | 2006         |
| 7       | Airplane Sierra  | 2006         |
+---------+------------------+--------------+

Fetch

Fetch is similar to limit, however it's SQL standard whereas LIMIT is not SQL standard. Using Fetch will allow us to migrate to a different db later on if we need to

FETCH { FIRST | NEXT} {number} {ROW | ROWS} ONLY

We can use FIRST or NEXT interchangably, likewise for ROW and ROWS

For example the query above

postgres@localhost:dvdrental> select film_id, title, rental_rate from film order by film_id offset 3 fetch first 4 rows only;
+---------+------------------+-------------+
| film_id | title            | rental_rate |
|---------+------------------+-------------|
| 4       | Affair Prejudice | 2.99        |
| 5       | African Egg      | 2.99        |
| 6       | Agent Truman     | 2.99        |
| 7       | Airplane Sierra  | 4.99        |
+---------+------------------+-------------+
SELECT 4
Time: 0.004s

Or we can also do like

postgres@localhost:dvdrental> select film_id, title, rental_rate from film order by film_id offset 3 fetch first rows only;
+---------+------------------+-------------+
| film_id | title            | rental_rate |
|---------+------------------+-------------|
| 4       | Affair Prejudice | 2.99        |
+---------+------------------+-------------+
SELECT 1
Time: 0.007s

In

A good example of using in is to use with date. For example

postgres@localhost:dvdrental> select payment_id, amount, payment_date from payment where payment_date::date in ('2007-02-15', '2007-02-16') limit 2;
+------------+--------+----------------------------+
| payment_id | amount | payment_date               |
|------------+--------+----------------------------|
| 17503      | 7.99   | 2007-02-15 22:25:46.996577 |
| 17504      | 1.99   | 2007-02-16 17:23:14.996577 |
+------------+--------+----------------------------+
SELECT 2
Time: 0.007s

The :: here is the cast operator. To select the date part only. So for example: 2007-02-15 22:25:46.996577 will cast to 2007-02-15

We can also use NOT IN

postgres@localhost:dvdrental> select film_id, title from film where film_id not in (1,2,3) order by film_id limit 5;
+---------+------------------+
| film_id | title            |
|---------+------------------|
| 4       | Affair Prejudice |
| 5       | African Egg      |
| 6       | Agent Truman     |
| 7       | Airplane Sierra  |
| 8       | Airport Pollock  |
+---------+------------------+
SELECT 5
Time: 0.007s

Between

Between is inclusive, so between low and high would be the same as value >= low AND value <= high

Between is also applied to date-range.

postgres@localhost:dvdrental> select payment_id, amount, payment_date from payment where payment_date between '2007-02-15' and '2007-02-20' limit 10;
+------------+--------+----------------------------+
| payment_id | amount | payment_date               |
|------------+--------+----------------------------|
| 17503      | 7.99   | 2007-02-15 22:25:46.996577 |
| 17504      | 1.99   | 2007-02-16 17:23:14.996577 |
| 17505      | 7.99   | 2007-02-16 22:41:45.996577 |
| 17506      | 2.99   | 2007-02-19 19:39:56.996577 |
| 17509      | 5.99   | 2007-02-17 23:58:17.996577 |
| 17512      | 4.99   | 2007-02-16 00:10:50.996577 |
| 17513      | 6.99   | 2007-02-16 01:15:33.996577 |
| 17514      | 0.99   | 2007-02-17 01:26:00.996577 |
| 17515      | 0.99   | 2007-02-17 04:32:51.996577 |
| 17516      | 6.99   | 2007-02-18 18:26:38.996577 |
+------------+--------+----------------------------+
SELECT 10
Time: 0.004s

it will compares to the timestamp midnight so in this example:

• '2007-02-15' becomes '2007-02-15 00:00:00'
• '2007-02-20' becomes '2007-02-20 00:00:00'

Like

2 wild cards

• % matches any sequence of 0 or more chharacters
• _ matches any single character

postgres@localhost:dvdrental> select 'apple' like 'app_e' result;
+--------+
| result |
|--------|
| True   |
+--------+
SELECT 1
Time: 0.006s
postgres@localhost:dvdrental> select 'apple' like 'app__s' result;
+--------+
| result |
|--------|
| False  |
+--------+
SELECT 1
Time: 0.006s

ILIKE

ILIKE is similar to LIKE but ignore case

postgres@localhost:dvdrental> select 'apple' ilike 'App__' result;
+--------+
| result |
|--------|
| True   |
+--------+
SELECT 1
Time: 0.006s

Operators

Escape

Sometimes if the content has _ or % we need to use ESCAPE with LIKE to specify the escape character.

postgres@localhost:dvdrental> select * from t;
+----------------------------------------------+
| message                                      |
|----------------------------------------------|
| the rents are now 10% higher than last month |
| the new film will have _ in the title        |
+----------------------------------------------+

For example we can make the query like

select * from t where message like '%\_%' escape '\'
+---------------------------------------+
| message                               |
|---------------------------------------|
| the new film will have _ in the title |
+---------------------------------------+
SELECT 1
Time: 0.007s

We specified that the _ in the LIKE statement is a _ not an expression because of the escape character is defined as \.

Is Null

Since null != null we need is null

postgres@localhost:dvdrental> select null = null result;
+--------+
| result |
|--------|
| <null> |
+--------+
SELECT 1
Time: 0.008s
postgres@localhost:dvdrental> select null is null result;
+--------+
| result |
|--------|
| True   |
+--------+
SELECT 1
Time: 0.006s

Similarly, we have is not null