Functions and Expressions

An expression computes a value from columns, literals, operators, and functions. Expressions can appear in SELECT, WHERE, ORDER BY, GROUP BY, and inside aggregates.

Example 1: CASE, searched and simple

The searched form tests conditions in order and returns the first match. ELSE is optional; without it, unmatched rows give NULL.

SQL

SELECT name, salary,
       CASE WHEN salary >= 180000 THEN 'high'
            WHEN salary >= 100000 THEN 'mid'
            ELSE 'low'
       END AS band
FROM employees
WHERE id <= 6
ORDER BY id;

Output

 name  | salary | band 
-------+--------+------
 Meera | 250000 | high
 Ravi  | 180000 | high
 Kiran | 150000 | mid
 Neha  | 150000 | mid
 Arjun | 110000 | mid
 Divya | 160000 | mid
(6 rows)

The simple form compares one expression to values:

SQL

SELECT id, status,
       CASE status
            WHEN 'delivered' THEN 'done'
            WHEN 'cancelled' THEN 'done'
            WHEN 'placed'    THEN 'open'
            ELSE 'in transit'
       END AS stage
FROM orders
ORDER BY id;

Output

 id |  status   |   stage    
----+-----------+------------
  1 | delivered | done
  2 | delivered | done
  3 | delivered | done
  4 | cancelled | done
  5 | delivered | done
  6 | shipped   | in transit
  7 | placed    | open
  8 | delivered | done
  9 | placed    | open
(9 rows)

The simple form uses =, so it cannot test NULL or ranges. Use the searched form for those.

Example 2: CASE in ORDER BY and inside aggregates

A custom sort order in ORDER BY:

SQL

SELECT id, status
FROM orders
ORDER BY CASE status WHEN 'placed' THEN 1 WHEN 'shipped' THEN 2 WHEN 'delivered' THEN 3 ELSE 4 END, id;

Output

 id |  status   
----+-----------
  7 | placed
  9 | placed
  6 | shipped
  1 | delivered
  2 | delivered
  3 | delivered
  5 | delivered
  8 | delivered
  4 | cancelled
(9 rows)

Conditional counts and sums, pivoting rows into columns. CASE without ELSE returns NULL, which count and sum skip.

SQL

SELECT count(*) AS total,
       count(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

Output

 total | delivered | cancelled 
-------+-----------+-----------
     9 |         5 |         1
(1 row)

PostgreSQL also has count(*) FILTER (WHERE status = 'delivered'), which does the same. MySQL, SQL Server, and Oracle do not support FILTER; use CASE there.

Example 3: concatenation, LIKE, and ILIKE

|| concatenates and returns NULL if any operand is NULL. concat() treats NULL as an empty string. concat_ws(sep, ...) adds a separator and skips NULLs.

SQL

SELECT name || ' (' || city || ')' AS label,
       concat(name, '-', NULL, '-', city) AS with_concat,
       name || NULL AS with_operator,
       concat_ws(', ', name, city) AS joined
FROM customers
WHERE id = 1;

Output

       label       |   with_concat    | with_operator |      joined      
-------------------+------------------+---------------+------------------
 Aarav (Bengaluru) | Aarav--Bengaluru |               | Aarav, Bengaluru
(1 row)

LIKE matches patterns: % is any run of characters and _ is exactly one. ILIKE ignores case. LIKE is case-sensitive in PostgreSQL; in MySQL and SQL Server the default collation makes it case-insensitive.

SQL

SELECT name
FROM employees
WHERE name LIKE '%a'
   OR name ILIKE 'r%'
ORDER BY name;

Output

 name  
-------
 Anita
 Divya
 Meera
 Neha
 Pooja
 Ravi
 Rohan
 Tara
(8 rows)

Example 4: substring, position, replace, trim, case, split_part

SQL

SELECT substring('PostgreSQL' FROM 1 FOR 4)  AS first_four,
       substr('PostgreSQL', 5)               AS from_fifth,
       position('SQL' IN 'PostgreSQL')       AS pos,
       replace('a-b-c', '-', '/')            AS replaced,
       '[' || trim('  padded  ') || ']'      AS trimmed,
       trim(BOTH 'x' FROM 'xxabcxx')         AS trimmed_x,
       upper('abc') AS up, lower('ABC') AS low, initcap('hello world') AS init,
       length('PostgreSQL')                  AS len,
       left('PostgreSQL', 4) AS lft, right('PostgreSQL', 3) AS rgt,
       split_part('a,b,c', ',', 2)           AS second_part;

Output

 first_four | from_fifth | pos | replaced | trimmed  | trimmed_x | up  | low |    init     | len | lft  | rgt | second_part 
------------+------------+-----+----------+----------+-----------+-----+-----+-------------+-----+------+-----+-------------
 Post       | greSQL     |   8 | a/b/c    | [padded] | abc       | ABC | abc | Hello World |  10 | Post | SQL | b
(1 row)

position returns 0 when nothing matches. String positions start at 1, not 0.

Example 5: regular expressions

Operators: ~ matches, ~* matches ignoring case, !~ does not match. Functions: regexp_replace, substring(text FROM pattern).

SQL

SELECT name
FROM employees
WHERE name ~ '^[A-K]'
ORDER BY name;

Output

 name  
-------
 Anita
 Arjun
 Divya
 Kiran
(4 rows)

SQL

SELECT regexp_replace('order-2024-06-01', '[0-9]+', '#', 'g') AS masked,
       substring('order-2024-06-01' FROM '[0-9]{4}')          AS year_part,
       'Order' ~* '^order'                                    AS case_insensitive;

Output

   masked    | year_part | case_insensitive 
-------------+-----------+------------------
 order-#-#-# | 2024      | t
(1 row)

The 'g' flag replaces every match; without it only the first is replaced.

Example 6: numeric functions and integer division

Dividing two integers gives an integer, truncated toward zero. Cast one side to numeric to keep the fraction.

SQL

SELECT 7 / 2                AS int_div,
       7 % 2                AS int_mod,
       mod(-7, 2)           AS neg_mod,
       7 / 2.0              AS numeric_div,
       7::numeric / 2       AS cast_div,
       round(7 / 2.0)       AS rounded,
       round(2.5) AS r_half_numeric, round(2.5::float8) AS r_half_float,
       round(3.14159, 2)    AS two_places,
       round(1234, -2)      AS hundreds,
       ceil(2.1) AS up, floor(2.9) AS down, ceil(-2.1) AS neg_up, floor(-2.1) AS neg_down,
       trunc(2.9) AS truncated, abs(-5) AS absolute;

Output

 int_div | int_mod | neg_mod |    numeric_div     |      cast_div      | rounded | r_half_numeric | r_half_float | two_places | hundreds | up | down | neg_up | neg_down | truncated | absolute 
---------+---------+---------+--------------------+--------------------+---------+----------------+--------------+------------+----------+----+------+--------+----------+-----------+----------
       3 |       1 |      -1 | 3.5000000000000000 | 3.5000000000000000 |       4 |              3 |            2 |       3.14 |     1200 |  3 |    2 |     -2 |       -3 |         2 |        5
(1 row)

round on numeric rounds halves away from zero. On double precision it rounds halves to even. SQL Server also truncates integer division. MySQL / and Oracle / return a decimal.

A practical use, average salary per department with two decimals:

SQL

SELECT dept_id, round(avg(salary), 2) AS avg_salary, avg(salary)::int AS avg_int
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

Output

 dept_id | avg_salary | avg_int 
---------+------------+---------
       1 |  168000.00 |  168000
       2 |  111666.67 |  111667
       3 |  100000.00 |  100000
       4 |   95000.00 |   95000
         |   70000.00 |   70000
(5 rows)

Example 7: date and time with fixed dates

Use literal dates so results do not change. date_trunc cuts a timestamp down to a unit, extract pulls out one field, and date + integer adds days.

SQL

SELECT date_trunc('month', DATE '2024-06-15')::date   AS month_start,
       date_trunc('year',  TIMESTAMP '2024-06-15 13:45:10') AS year_start,
       extract(year  FROM DATE '2024-06-15')           AS yr,
       extract(month FROM DATE '2024-06-15')           AS mon,
       extract(dow   FROM DATE '2024-06-15')           AS dow_sun0,
       extract(isodow FROM DATE '2024-06-15')          AS dow_mon1,
       DATE '2024-06-15' + 30                          AS plus_30_days,
       DATE '2024-06-15' - DATE '2024-06-01'           AS days_between;

Output

 month_start |     year_start      |  yr  | mon | dow_sun0 | dow_mon1 | plus_30_days | days_between 
-------------+---------------------+------+-----+----------+----------+--------------+--------------
 2024-06-01  | 2024-01-01 00:00:00 | 2024 |   6 |        6 |        6 | 2024-07-15   |           14
(1 row)

dow counts Sunday as 0; isodow counts Monday as 1 and Sunday as 7.

Example 8: intervals, age, and to_char

Adding an interval to a date returns a timestamp. Month arithmetic clamps to the end of the month. age gives years, months, and days between two dates.

SQL

SELECT DATE '2024-01-31' + INTERVAL '1 month'           AS clamped,
       DATE '2024-06-15' + INTERVAL '2 weeks 3 days'    AS plus_interval,
       TIMESTAMP '2024-06-15 10:00' - INTERVAL '90 minutes' AS minus_interval,
       age(DATE '2024-06-15', DATE '2015-03-01')        AS tenure,
       extract(year FROM age(DATE '2024-06-15', DATE '2015-03-01')) AS whole_years,
       to_char(DATE '2024-06-15', 'YYYY-MM-DD')         AS iso,
       to_char(DATE '2024-06-15', 'DD Mon YYYY')        AS pretty,
       to_char(DATE '2024-06-15', 'Day')                AS weekday,
       to_char(1234567.891, 'FM9,999,999.00')           AS money;

Output

       clamped       |    plus_interval    |   minus_interval    |         tenure         | whole_years |    iso     |   pretty    |  weekday  |    money     
---------------------+---------------------+---------------------+------------------------+-------------+------------+-------------+-----------+--------------
 2024-02-29 00:00:00 | 2024-07-02 00:00:00 | 2024-06-15 08:30:00 | 9 years 3 mons 14 days |           9 | 2024-06-15 | 15 Jun 2024 | Saturday  | 1,234,567.89
(1 row)

Employees hired in 2019 or later, with tenure at a fixed reference date:

SQL

SELECT name, hired_on,
       age(DATE '2024-12-31', hired_on) AS tenure,
       to_char(hired_on, 'Mon YYYY')    AS hired
FROM employees
WHERE hired_on >= DATE '2019-01-01'
ORDER BY hired_on;

Output

  name  |  hired_on  |         tenure         |  hired   
--------+------------+------------------------+----------
 Rohan  | 2019-04-22 | 5 years 8 mons 9 days  | Apr 2019
 Neha   | 2019-09-01 | 5 years 3 mons 30 days | Sep 2019
 Sameer | 2020-06-30 | 4 years 6 mons 1 day   | Jun 2020
 Arjun  | 2021-05-20 | 3 years 7 mons 11 days | May 2021
 Pooja  | 2022-03-14 | 2 years 9 mons 17 days | Mar 2022
 Anita  | 2023-01-09 | 1 year 11 mons 22 days | Jan 2023
 Tara   | 2024-02-01 | 10 mons 30 days        | Feb 2024
(7 rows)

Filter dates with a range (>= start AND < next_start) rather than wrapping the column in date_trunc or extract; a range lets an index on the column be used.

Example 9: type casts and COALESCE

expr::type is PostgreSQL syntax. CAST(expr AS type) is standard SQL and works in every database. Casting a numeric to int rounds (3.99 becomes 4). A failed cast raises an error.

SQL

SELECT '42'::int + 1                        AS shorthand,
       CAST('2024-06-15' AS date) + 1       AS standard,
       CAST(3.99 AS int)                    AS rounds_not_truncates,
       12345::text || ' units'              AS to_text,
       '3.50'::numeric(5,1)                 AS numeric_scale,
       true::int                            AS bool_as_int;

Output

 shorthand |  standard  | rounds_not_truncates |   to_text   | numeric_scale | bool_as_int 
-----------+------------+----------------------+-------------+---------------+-------------
        43 | 2024-06-16 |                    4 | 12345 units |           3.5 |           1
(1 row)

SQL

SELECT 'abc'::int;

Output

ERROR:  invalid input syntax for type integer: "abc"
LINE 1: SELECT 'abc'::int;
               ^

COALESCE(a, b, ...) returns the first non-NULL argument. Typical uses are defaults and safe arithmetic. See NULL handling.

SQL

SELECT e.name,
       coalesce(d.name, 'unassigned')   AS department,
       coalesce(e.manager_id, 0)        AS manager_id,
       nullif(e.dept_id, 1)             AS not_engineering
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
WHERE e.id IN (1, 6, 12)
ORDER BY e.id;

Output

 name  | department  | manager_id | not_engineering 
-------+-------------+------------+-----------------
 Meera | Engineering |          0 |                
 Divya | Sales       |          1 |               2
 Tara  | unassigned  |          1 |                
(3 rows)

nullif(a, b) returns NULL when the two are equal. It is the standard guard against division by zero: x / nullif(y, 0).

Example 10: generate_series

generate_series(start, stop, step) produces rows. It works on integers and on dates or timestamps with an interval step.

SQL

SELECT n, n * n AS square
FROM generate_series(1, 5) AS n;

Output

 n | square 
---+--------
 1 |      1
 2 |      4
 3 |      9
 4 |     16
 5 |     25
(5 rows)

Filling gaps: every month from May to August 2024 with its order count, including months with no orders.

SQL

SELECT to_char(m, 'YYYY-MM') AS month, count(o.id) AS orders
FROM generate_series(DATE '2024-05-01', DATE '2024-08-01', INTERVAL '1 month') AS m
LEFT JOIN orders o ON date_trunc('month', o.order_date) = m
GROUP BY m
ORDER BY m;

Output

  month  | orders 
---------+--------
 2024-05 |      0
 2024-06 |      4
 2024-07 |      5
 2024-08 |      0
(4 rows)

The same idea with dates one day at a time gives a calendar table without storing one.

Example 11: string_agg and array_agg

Aggregates that collect values from a group into one value. ORDER BY inside the call fixes the element order.

SQL

SELECT d.name AS department,
       string_agg(e.name, ', ' ORDER BY e.name) AS employees,
       array_agg(e.salary ORDER BY e.salary DESC) AS salaries
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.name
ORDER BY d.name;

Output

 department  |            employees            |               salaries               
-------------+---------------------------------+--------------------------------------
 Engineering | Arjun, Kiran, Meera, Neha, Ravi | {250000,180000,150000,150000,110000}
 HR          | Rohan                           | {95000}
 Marketing   | Anita, Vikram                   | {120000,80000}
 Sales       | Divya, Pooja, Sameer            | {160000,90000,85000}
(4 rows)

unnest turns an array back into rows:

SQL

SELECT unnest(ARRAY['a', 'b', 'c']) AS item;

Output

 item 
------
 a
 b
 c
(3 rows)

MySQL uses GROUP_CONCAT, SQL Server STRING_AGG, Oracle LISTAGG.

Example 12: VALUES lists

VALUES builds a table inline. Alias it and name the columns. Use it for lookup data in a query, or as a test input.

SQL

SELECT o.id, o.status, s.sort_key
FROM orders o
JOIN (VALUES ('placed', 1), ('shipped', 2), ('delivered', 3), ('cancelled', 4)) AS s(status, sort_key)
  ON s.status = o.status
ORDER BY s.sort_key, o.id;

Output

 id |  status   | sort_key 
----+-----------+----------
  7 | placed    |        1
  9 | placed    |        1
  6 | shipped   |        2
  1 | delivered |        3
  2 | delivered |        3
  3 | delivered |        3
  5 | delivered |        3
  8 | delivered |        3
  4 | cancelled |        4
(9 rows)

A standalone VALUES works as a query with columns named column1, column2, and so on:

SQL

VALUES (1, 'one'), (2, 'two'), (3, 'three');

Output

 column1 | column2 
---------+---------
       1 | one
       2 | two
       3 | three
(3 rows)

Interview points

Question	Answer
`7 / 2` returns?	`3`. Integer division. Use `7 / 2.0` or `7::numeric / 2`.
`NULL
`CASE` with no match and no `ELSE`?	`NULL`.
`CAST` vs `::`	Same result. `CAST` is standard SQL, `::` is PostgreSQL only.
Why not `WHERE extract(year FROM col) = 2024`?	The function on the column prevents a plain index on `col` from being used. Use a range.

Summary

CASE works in SELECT, ORDER BY, and inside aggregates; no match without ELSE gives NULL.
Integer division truncates; cast to numeric to keep fractions.
Use date_trunc, extract, interval arithmetic, and age for dates. Filter with ranges.
COALESCE supplies defaults and nullif avoids division by zero.
generate_series, string_agg, array_agg, and VALUES build and collapse row sets.