Postgres by Example: String and Numeric Functions

PostgreSQL ships with hundreds of built-in functions. The string and math families cover most day-to-day needs: case conversion, padding, trimming, splitting, replacing, hashing; rounding, absolute value, modulus, exponentiation, random. Get comfortable with the common ones and you will reach for them constantly.

What you'll learn:

• Common string functions: upper, lower, length, substring, position, trim, replace, split_part
• String concatenation with || and concat
• Regex with ~, ~*, regexp_replace, regexp_match
• Common numeric functions: abs, round, ceil, floor, mod, power, random
• The \df meta-command to discover more

-- Case and length
SELECT upper('hello') AS u, lower('HELLO') AS l, length('hello') AS len;

-- Substring: SQL syntax and function syntax
SELECT substring('postgresql' FROM 1 FOR 8) AS sql_form,
       substring('postgresql', 1, 8)        AS fn_form;

-- Position: 1-based index, 0 if not found
SELECT position('gre' IN 'postgresql') AS pos;

-- Trim and replace
SELECT trim('  hi  ')                         AS trimmed,
       trim(both '/' FROM '/path/to/file/')   AS path_trim,
       replace('a-b-c', '-', '.')             AS replaced;

-- Splitting
SELECT split_part('a.b.c.d', '.', 2)                  AS second_part,
       string_to_array('a,b,c', ',')                  AS as_array;

-- Concatenation: || vs concat. concat tolerates NULL; || does not.
SELECT 'a' || NULL AS pipe_null,
       concat('a', NULL, 'b') AS concat_null;

-- Regex: ~ matches, ~* is case-insensitive
SELECT 'PostgreSQL' ~ 'gres' AS match,
       'PostgreSQL' ~* 'POSTGRE' AS imatch,
       regexp_replace('a1b2c3', '[0-9]', '#', 'g') AS no_digits;

-- Numeric: abs, round, ceil, floor, mod, power, random
SELECT abs(-5)                AS a,
       round(3.14159, 2)      AS r,
       ceil(2.1)              AS c,
       floor(2.9)             AS f,
       mod(10, 3)             AS m,
       power(2, 10)           AS p,
       round((random() * 100)::numeric, 2) AS rand;

substring has two forms: the SQL-standard substring(str FROM start FOR n) and the function-call style substring(str, start, n). Both are 1-based.

concat and || differ on NULL: 'a' || NULL is NULL (any operand makes the whole expression NULL), while concat('a', NULL, 'b') returns 'ab' — concat treats NULLs as empty strings. Pick the behavior you want explicitly.

The regex operators ~ and ~* are matching shortcuts. regexp_replace(text, pattern, replacement [, flags]) is the workhorse for substitutions; the 'g' flag means "replace all matches" (default is first only). regexp_match returns the captured groups.

Numeric functions are mostly self-explanatory. random() returns a double precision in [0, 1); multiply and cast to get the range you want. For cryptographically secure random, use gen_random_bytes from pgcrypto.

To run:

$ psql -f source/string-and-numeric-functions.sql postgres
   u   |   l   | len
-------+-------+-----
 HELLO | hello |   5
(1 row)
 ...

Common pitfalls:

• Confusing substring indexing with zero-based languages — PostgreSQL is 1-based.
• replace is a literal-string replacement, not a regex. For regex, use regexp_replace.
• random() is not cryptographically secure. For security-sensitive cases, use pgcrypto's gen_random_bytes.
• length counts characters; octet_length counts bytes. They differ for multibyte text (UTF-8).
• round(0.5) follows banker's rounding (to even): round(0.5) is 0, round(1.5) is 2. Pre-cast to numeric for predictable behavior.

Tip: In interactive psql, \df *upper* lists every function with "upper" in the name. \df+ upper shows the source/owner. Excellent way to discover what is available.

Try it: Build a label: SELECT upper(name) || ' [' || length(name) || ']' FROM fruits;. Round all averages: SELECT customer_id, round(avg(total), 2) FROM orders_example GROUP BY customer_id;. Use regex to find names starting with a vowel: SELECT name FROM fruits WHERE name ~* '^[aeiou]';.

Source: string-and-numeric-functions.sql

Next: Date Functions and COALESCE

Home: Postgres by Example