Built-in SQL Functions

Raw column values are rarely what the report needs. Emails need a username extracted, timestamps need to become months, prices need bucketing into tiers, missing phones need a readable placeholder.

You can do all of it in SQL, close to the data, without pulling rows into application code to loop over them. Function names vary across databases. The concepts do not, and the PostgreSQL names below have close equivalents elsewhere.

String functions#

SELECT full_name,
       UPPER(city)                     AS city_upper,
       LENGTH(email)                   AS email_len,
       SPLIT_PART(email, '@', 1)       AS handle,
       SUBSTRING(full_name FROM 1 FOR 3) AS first3
FROM customers
LIMIT 3;

  full_name   | city_upper | email_len | handle | first3
--------------+------------+-----------+--------+--------
 Aisha Khan   | MUMBAI     |        17 | aisha  | Ais
 Ben Carter   | AUSTIN     |        15 | ben    | Ben
 Chloe Dubois | LYON       |        17 | chloe  | Chl

The ones you will use constantly:

CONCAT and CONCAT_WS skip NULL arguments. The || operator does not: one NULL makes the whole string NULL. For building labels from optional parts, the function is almost always what you want.

String positions are 1-based, not 0-based. This catches programmers coming from every other language.

Date and time functions#

Date arithmetic is where PostgreSQL shines. Dates subtract into day counts, and intervals add cleanly.

SELECT DATE '2024-03-15' + 30                      AS plus_30_days,   -- 2024-04-14
       DATE '2024-03-15' - DATE '2024-02-01'       AS days_between,   -- 43
       EXTRACT(MONTH FROM DATE '2024-03-15')       AS month_number,   -- 3
       TO_CHAR(DATE '2024-03-15', 'DD Mon YYYY')   AS pretty,         -- 15 Mar 2024
       TIMESTAMP '2024-03-15 09:00' + INTERVAL '2 hours 30 minutes' AS later;
                                                                      -- 2024-03-15 11:30:00

Two functions cover most reporting work. EXTRACT pulls out a part (year, month, day of week). DATE_TRUNC rounds a timestamp down to the start of a unit, which makes it the right tool for grouping.

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-15 14:30:00');
-- 2024-03-01 00:00:00

Monthly revenue, grouped properly:

SELECT DATE_TRUNC('month', o.order_date)::date AS month,
       SUM(oi.quantity * oi.unit_price)        AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY 1
ORDER BY 1;

   month    | revenue
------------+---------
 2024-02-01 |  439.00
 2024-03-01 |  501.50
 2024-04-01 | 1229.50
 2024-05-01 |  710.00
 2024-06-01 |  259.50

CURRENT_DATE gives today's date and NOW() gives the current timestamp with time zone. Both are fixed for the duration of a transaction, so two calls in one transaction return the same value.

Time zones#

With TIMESTAMPTZ, the database stores an exact instant and displays it in the session's time zone. Convert explicitly when you need a particular one:

SELECT NOW() AT TIME ZONE 'Asia/Kolkata';

If a "daily" report looks wrong around midnight, suspect the time zone before you suspect the query.

Numeric functions#

SELECT ROUND(147.7727, 2) AS r,      -- 147.77
       CEIL(4.2)          AS c,      -- 5
       FLOOR(4.8)         AS f,      -- 4
       ABS(-7)            AS a,      -- 7
       MOD(17, 5)         AS m,      -- 2
       POWER(2, 10)       AS p;      -- 1024

Round at the end. Rounding each line item and then summing gives different answers than summing and then rounding, and finance teams notice the difference of one paisa.

Casting#

Convert between types with CAST or the PostgreSQL shorthand ::.

SELECT CAST('42' AS INTEGER), '42'::integer, 7::numeric / 2, '2024-03-15'::date;

A bad value stops the query:

ERROR:  invalid input syntax for type integer: "abc"

That is why cleaning messy imports needs a guarded conversion, which Module 7 covers.

CASE: conditional logic inside a query#

CASE is SQL's if/else. It returns the value of the first branch whose condition is true.

SELECT name, unit_price,
       CASE WHEN unit_price >= 300 THEN 'premium'
            WHEN unit_price >= 50  THEN 'mid'
            ELSE 'budget'
       END AS tier
FROM products
ORDER BY unit_price DESC;

            name             | unit_price |  tier
-----------------------------+------------+---------
 Standing Desk               |     499.00 | premium
 Ergonomic Chair             |     349.00 | premium
 27-inch Monitor             |     289.00 | mid
 Noise-Cancelling Headphones |     199.00 | mid
 Mechanical Keyboard         |      89.00 | mid
 HD Webcam                   |      59.00 | mid
 USB-C Hub                   |      45.00 | budget
 Laptop Stand                |      34.00 | budget
 Wireless Mouse              |      29.50 | budget
 Desk Lamp                   |      24.00 | budget
 Cable Organizer             |       9.00 | budget

Branches are checked top to bottom and the first match wins, so order matters. With no match and no ELSE, the result is NULL. That quiet NULL is the most common CASE bug.

CASE works in ORDER BY for custom sorting, and inside aggregates for pivots:

ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 WHEN 'shipped' THEN 3 ELSE 4 END

COALESCE and NULLIF#

COALESCE returns the first non-null argument. It is the standard way to supply a default.

SELECT full_name, COALESCE(phone, 'no phone on file') AS contact
FROM customers ORDER BY customer_id LIMIT 5;

   full_name   |     contact
---------------+------------------
 Aisha Khan    | +91-98450-11122
 Ben Carter    | +1-512-555-0142
 Chloe Dubois  | no phone on file
 Diego Alvarez | +34-91-555-0199
 Elena Petrova | no phone on file

NULLIF(a, b) does the reverse: it returns NULL when the two arguments are equal. Its best use is protecting a division.

SELECT revenue / NULLIF(order_count, 0) AS avg_order_value FROM monthly_stats;

A zero count would raise division by zero. With NULLIF it produces NULL, which is the honest answer: there is no average of nothing.

Under the hood: functions and indexes#

A function applied to a column in WHERE usually stops an ordinary index on that column from being used.

WHERE EXTRACT(YEAR FROM order_date) = 2024      -- must compute for every row
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01'                -- can use an index on order_date

The second form is called sargable: the database can search for it directly. Write filters that way. When you genuinely need the function, build an expression index on it, as Module 6 shows.

Common mistakes#

CASE with no ELSE. Unmatched rows become NULL and disappear from later filters.

Concatenating with || when a part may be NULL. The whole label vanishes. Use CONCAT_WS or COALESCE.

Integer division in averages. SUM(x) / COUNT(*) on integers truncates. Cast one side to NUMERIC.

Grouping by a raw timestamp. Every distinct second becomes its own group. Truncate to the unit you mean.

Wrapping the filtered column in a function. You turn an index lookup into a full scan.

Practice#

1. Produce each customer's email domain (the part after @) and count customers per domain. Everyone should land on example.com.
2. For every order, show order_date and the name of the weekday it fell on (TO_CHAR(order_date, 'Day')). Which weekday has the most orders?
3. Label each order 'open' for pending or paid, 'done' for delivered or shipped, and 'void' for cancelled, using CASE. Then count orders per label: you should get open 3, done 10, void 1.
4. Compute average order value per month, protecting against zero orders with NULLIF.
5. Rewrite WHERE TO_CHAR(order_date, 'YYYY-MM') = '2024-04' as a sargable range filter.