PostgreSQL text, number and type functions

PostgreSQL ships with hundreds of functions. The ones below cover most day-to-day work.

Text: lower, upper, initcap, length, trim, substring, left/right, position, replace, split_part, concat, concat_ws, lpad/rpad, format, reverse.

Numbers: round, trunc, ceil, floor, abs, mod (or %), power, sqrt, random(), width_bucket.

Casting converts between types. PostgreSQL has two spellings: the standard CAST(x AS type) and the shorter x::type. pg_typeof(x) tells you what type a value is.

Syntax

function_name(arg1, arg2)
CAST(value AS type)   -- or   value::type

Examples

Edit the code and run it. Output is printed the way psql prints it.

11.1Cleaning and shaping text

SELECT name,
       upper(name)                AS upper_name,
       length(name)               AS chars,
       left(name, 4)              AS first4,
       replace(name, ' ', '_')    AS slug_ish,
       lower(regexp_replace(name, '[^a-zA-Z0-9]+', '-', 'g')) AS slug
FROM products
LIMIT 6;

regexp_replace with the 'g' flag replaces every run of non-alphanumeric characters with a dash, making a URL-friendly slug.

11.2Splitting and formatting

SELECT email,
       split_part(email, '@', 1) AS username,
       split_part(email, '@', 2) AS domain,
       concat_ws(' / ', name, city, country) AS label,
       format('%s has tier %L', name, tier) AS sentence
FROM customers;

split_part(text, delimiter, n) returns the n-th piece. concat_ws joins with a separator and skips NULLs (look at Mia). In format, %s inserts a value and %L inserts it as a quoted literal.

11.3Number functions

SELECT 19.987 AS n,
       round(19.987, 1) AS round1,
       trunc(19.987, 1) AS trunc1,
       ceil(19.2)  AS ceil,
       floor(19.8) AS floor,
       17 % 5      AS remainder,
       power(2, 10) AS two_pow_10,
       round(random() * 100) AS random_0_100;

round rounds, trunc chops. random() returns a different value each run, between 0 and 1.

11.4Casting between types

SELECT '42'::integer + 1          AS int_math,
       CAST('2024-12-25' AS date) AS xmas,
       '3.14159'::numeric(5,2)    AS rounded_numeric,
       123::text || ' items'      AS as_text,
       'yes'::boolean             AS bool_from_text,
       pg_typeof(1.5)             AS type_of_1_5;

Text in quotes is converted to the requested type. An impossible cast such as 'abc'::integer raises an error; try it.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 11.1

List every distinct email domain (the part after @) used by customers.

Hint

split_part(email, '@', 2) with DISTINCT

Solution

SELECT DISTINCT split_part(email, '@', 2) AS domain FROM customers;

Exercise 11.2

Show employee initials (e.g. “A.R.”) and email in upper case as email_upper.

Hint

left(first_name,1) || '.' || ...

Solution

SELECT left(first_name, 1) || '.' || left(last_name, 1) || '.' AS initials, upper(email) AS email_upper FROM employees;