11. Limiting number of records produced by query

Specifies the columns of the result set.

SELECT id, email
FROM users

Filters out duplicate records from the result set.

SELECT DISTINCT(country)
FROM users

If/else logic of SQL.

SELECT CASE WHEN age > 21 THEN 'adult' ELSE 'youngster' END
FROM users

It could be used without ELSE keyword (the default ELSE value is NULL):

SELECT COUNT(CASE WHEN status = 'customer' THEN id END)
FROM users

The CASE statement goes through all conditions and returns the first value when a condition is met (or ELSE value):

SELECT
  CASE
  WHEN age < 12 THEN 'child'
  WHEN age < 21 THEN 'teenager' ELSE 'adult'
  END AS age_category
FROM users

Specifies the tables we’re querying.

SELECT *
FROM purchases

Specifies one or multiple filters.

SELECT *
FROM users
WHERE
  country = 'us'

Returns records that have matching values in both left (the one in FROM clause) and right (the one in INNER JOIN) tables.

SELECT *
FROM purchases p
INNER JOIN users u
  ON u.id = p.user_id

Selects all records from the left table and matching records from the right table.

SELECT *
FROM users u
LEFT JOIN purchases p
  ON p.user_id = u.id

Groups records and returns a row for each group – result of an aggregate function.

SELECT
  country,
  COUNT(*)
FROM users
GROUP BY 1

Sorts records in the result set. The default order is ASC.

SELECT *
FROM purchases
ORDER BY created_at DESC

Sets the max number of records in the result set.

SELECT *
FROM books
LIMIT 10

Filters the records within a range. It’s an inclusive filter, basically a shortcut for >= ... AND <= ...

SELECT *
FROM users
WHERE
  signup_date BETWEEN '2018-01-01' AND '2018-02-01'

Combines two filter conditions. Evaluates to TRUE only if both conditions are TRUE.

SELECT *
FROM users
WHERE
  country = 'us'
  AND status = 'customer'

Combines two filter conditions. Evaluates to TRUE if any condition is TRUE. In a query with many filters make sure to wrap two conditions with OR in brackets to avoid interferance with other filters.

SELECT *
FROM marketing_spends
WHERE
  utm_source = 'twitter'
  OR utm_source = 'facebook'

Filters records by matching a string pattern. Symbol % stands for any character (wildcard).

SELECT *
FROM users
WHERE
  email LIKE '%gmail.com'

Filters records that are present in a list or a subquery.

SELECT *
FROM users
WHERE
  country IN ('us', 'ca', 'au', 'gb')

Evaluates to TRUE if the value is NULL.

SELECT *
FROM users
WHERE
  utm_campaign IS NULL

Evaluates to TRUE if the value is not NULL.

SELECT *
FROM users
WHERE
  utm_campaign IS NOT NULL

Defines a CTE (Common Table Expression) or simply a subquery.

WITH customers AS (
  SELECT *
  FROM users
  WHERE
    status = 'customer'
)

SELECT *
FROM customers

Returns the current timestamp.

SELECT NOW()
-- Example result: 2020-02-22 22:16:16.634526+02

Returns the part of a timestamp or an interval as a number.

SELECT DATE_PART('day', '2020-02-14'::timestamp)
-- Result: 14

Returns the part of a timestamp as a rounded timestamp.

SELECT DATE_TRUNC('day', '2020-02-14'::timestamp)
-- Result: 2020-02-14 00:00:00

Returns a formatted timestamp as a string. Possible patterns that could be used in a format argument:

SELECT TO_CHAR('2020-02-14'::timestamp, 'YYYY-MM')
-- Result: 2020-02

The whole list of patterns could be found on the official PostgreSQL website.

Converts a string into a date.

SELECT TO_DATE('2020-02-14', 'YYYY-MM-DD')
-- Result: 2020-02-14

SELECT TO_DATE('02/14/2020', 'MM/DD/YYYY')
-- Result: 2020-02-14

Returns the absolute value of the number.

SELECT ABS(-100)
-- Result: 100

Returns a random float number between 0 and 1.

SELECT RANDOM()
-- Exampe result: 0.8502873764373362

Rounds a number to a certain precision.

SELECT ROUND(3.141592)
-- Result: 3

SELECT ROUND(3.141592, 4)
-- Result: 3.1416

Round a number to the closest higher integer.

SELECT CEIL(3.1415)
-- Result: 4

Round a number to the closest lower integer.

SELECT FLOOR(3.1415)
-- Result: 3

Truncates the number to a precision without rounding it.

SELECT TRUNC(3.141592, 5)
-- Result: 3.14159

SELECT TRUNC(3.141592)
-- Result: 3

Returns the smallest value from the list of arguments.

SELECT LEAST(2, 1, 3)
-- Result: 1

Returns the highest value from the list of arguments.

SELECT GREATEST(1, 3, 2)
-- Result: 3

Returns the first non-NULL input argument. Used to specify default values.

SELECT COALESCE(NULL, NULL, 'foo', 'bar')
-- Result: foo

Returns NULL if arguments are equal. Returns the first argument otherwise.

SELECT
  NULLIF(123, 123),
  NULLIF('2020-02-14', '2020-02-15')
-- Result: NULL, '2020-02-14'

Gives each record in the partition a number (starting with 1).

SELECT
  ROW_NUMBER() OVER(PARTITION BY visitor_id ORDER BY created_at ASC),
  *
FROM web_analytics.pageviews

Returns the column value (or NULL) from the previous row in the partition (or a number of rows specified by the offset argument).

SELECT
  user_id,
  created_at,
  amount,
  LAG(amount) OVER(PARTITION BY user_id ORDER BY created_at ASC)
FROM purchases

Returns the column value (or NULL) from the next row in the partition (or a number of rows specified by the offset argument).

SELECT
  visitor_id,
  created_at,
  url,
  LEAD(url) OVER(PARTITION BY visitor_id ORDER BY created_at ASC)
FROM web_analytics.pageviews

Return value of the first row in the partition.

SELECT
  FIRST_VALUE(url) OVER(PARTITION BY visitor_id ORDER BY created_at ASC),
  *
FROM web_analytics.pageviews

Return value of the last row in the partition.

SELECT
  LAST_VALUE(url) OVER(PARTITION BY visitor_id ORDER BY created_at ASC),
  *
FROM web_analytics.pageviews

Returns the number of rows in the partition. NULL values are ignored.

SELECT
  COUNT(*) OVER (PARTITION BY country),
  *
FROM users

Returns the sum of values in the partition.

SELECT
  SUM(amount) OVER (),
  *
FROM purchases

Returns the average value in the partition.

SELECT
  AVG(age) OVER(PARTITION BY country),
  *
FROM users

Returns the max value in the partition.

SELECT
  MAX(age) OVER(PARTITION BY country),
  *
FROM users

Returns the min value in the partition.

SELECT
  MIN(age) OVER(PARTITION BY country),
  *
FROM users

Returns the number of rows. NULL values are ignored.

SELECT
  country,
  COUNT(*)
FROM users
GROUP BY 1

Returns the sum of values.

SELECT
  SUM(amount)
FROM purchases

Returns the average value.

SELECT
  AVG(age)
FROM users

Returns the max value.

SELECT
  country,
  MAX(age)
FROM users
GROUP BY 1

Returns the min value.

SELECT
  country,
  MIN(age)
FROM users
GROUP BY 1

Combines the results of two queries (stacks two result sets vertically). It filters out duplicate records.

SELECT *
FROM table_1
UNION
SELECT *
FROM table_2

Combines the results of two queries. It keeps duplicate records.

SELECT *
FROM table_1
UNION ALL
SELECT *
FROM table_2

Returns records from the first query that are not returned by the second query. It filters out duplicate records.

SELECT *
FROM table_1
EXCEPT
SELECT *
FROM table_2

Returns records from the first query that are not returned by the second query. It keeps duplicate records.

SELECT *
FROM table_1
EXCEPT ALL
SELECT *
FROM table_2

Returns records that are returned by both the first and second queries. It filters out duplicate records.

SELECT *
FROM table_1
INTERSECT
SELECT *
FROM table_2

Returns records that are returned by both the first and second queries. It keeps duplicate records.

SELECT *
FROM table_1
INTERSECT ALL
SELECT *
FROM table_2

Combines all input strings to one string.

SELECT CONCAT('Hello', ' ', 'world', '!')
-- Result: Hello world!

Returns a number of characters in the input text.

SELECT LENGTH('Hello world!')
-- Result: 12

Changes the case of text to the lower case.

SELECT LOWER('Hello world!')
-- Result: hello world!

Changes the case of text to the upper case.

SELECT LOWER('Hello world!')
-- Result: HELLO WORLD!

Reverses the input text.

SELECT REVERSE('foobar')
-- Result: raboof

Trims the number of characters from the beginning of a text.

SELECT LEFT('foobar', 3)
-- Result: foo

Trims the number of characters from the end of a text.

SELECT RIGHT('foobar', 3)
-- Result: bar

Adds new characters to the beginning of a text input until it has the specified length.

SELECT LPAD('8319', 16, 'X')
-- Result: XXXXXXXXXXXX8319

Adds new characters to the end of a text input until it has the specified length.

SELECT RPAD('1248', 8, '_')
-- Result: 1248____

Splits a text input by a substring into multiple parts and returns the specified part. The parts numbers start with 1.

SELECT SPLIT_PART('foo_bar', '_', 2)
-- Result: bar

Repeats a text input a number of times.

SELECT REPEAT('abc', 3)
-- Result: abcabcabc

Replaces a substring in a text with a new string.

SELECT REPLACE('foobar', 'foo', '')
-- Result: bar

Replaces substrings of a text which match an input pattern with a new string.

SELECT REGEXP_REPLACE('foobar@gmail.com', '\A\w+@', '')
-- Result: gmail.com

Returns the first matched group.

SELECT SUBSTRING('Sally (engineer)', '(\w+)\s\((\w+)\)')
-- Result: Sally

Returns an array of all captured substrings.

SELECT regexp_matches('Sally (engineer)', '(\w+)\s\((\w+)\)')
-- Result: {Sally,engineer}