PostgreSQL String, Regex and Formatting Functions: concat_ws, format(), split_part, regexp_replace, to_char and ICU Collations

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image, built with ICU), in the Bookstore lab we set up in post 02, right after reloading the schema.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, shows the title, the author and, if there is one, the translator of a book on one line of the book card. The translator column is empty for most books; below we represent it with NULL. We joined the same line in three different ways, then looked at the length of a Turkish book title:

SELECT b.title || ' / ' || a.name || ' / ' || NULL AS pipe,
       concat(b.title, ' / ', a.name, ' / ', NULL) AS concat_result,
       concat_ws(' / ', b.title, a.name, NULL, b.published_year) AS concat_ws_result
FROM books AS b
JOIN authors AS a ON a.author_id = b.author_id
WHERE b.book_id = 3;
SELECT length('Çalıkuşu') AS len, octet_length('Çalıkuşu') AS bytes,
       length('Dune '::char(10)) AS char_len, octet_length('Dune '::char(10)) AS char_bytes;

Which of the three columns comes back empty? How many characters and how many bytes is "Çalıkuşu"? What does length say for "Dune" stored in a char(10)?

 pipe |           concat_result            |            concat_ws_result
------+------------------------------------+----------------------------------------
      | Middlemarch #3 / Guido Dijkstra /  | Middlemarch #3 / Guido Dijkstra / 1934

 len | bytes | char_len | char_bytes
-----+-------+----------+------------
   8 |    11 |        4 |         10

|| is the standard SQL operator, and if one side is NULL, so is the result; the whole line disappeared. concat treats NULL as an empty string but keeps the separator, hence the trailing " / ". concat_ws skips a NULL argument together with its separator and converts the number to text by itself. Length is really two questions: length counts characters (8), octet_length counts UTF-8 bytes (ç, ı and ş take two bytes each, 11 in total). For char(n), length ignores trailing spaces, but the value takes 10 bytes on disk. In this post we go through the string, regex and formatting functions of PostgreSQL that come up most often in everyday work, on Bookstore data. The T-SQL equivalents are in the "T-SQL String Function'ları" post of the SQL Temelleri series; we do not repeat T-SQL here and mention it only where the difference matters.

Everyday Functions: Taking Apart, Cleaning, Padding

Taking Strings Apart: split_part, substring, strpos, left/right

Bookstore order numbers look like BK-00001042. To get the number inside, find the position of the separator and cut from either end:

SELECT order_number,
       split_part(order_number, '-', 2) AS digits,
       ltrim(split_part(order_number, '-', 2), '0')::int AS seq,
       split_part('2026-11-15', '-', -1) AS last_part,
       strpos(order_number, '-') AS dash_at,
       left(order_number, 2) AS prefix,
       right(order_number, 4) AS tail
FROM orders
WHERE order_id = 1042;
SELECT title,
       substring(title FROM '#(\d+)$') AS copy_no,
       substring(title, 1, strpos(title, ' #') - 1) AS base_title,
       overlay(title PLACING 'Dune Messiah' FROM 1 FOR 4) AS overlaid
FROM books
WHERE book_id = 20000;

 order_number |  digits  | seq  | last_part | dash_at | prefix | tail
--------------+----------+------+-----------+---------+--------+------
 BK-00001042  | 00001042 | 1042 | 15        |       3 | BK     | 1042

    title    | copy_no | base_title |      overlaid
-------------+---------+------------+---------------------
 Dune #20000 | 20000   | Dune       | Dune Messiah #20000

split_part returns the nth field for a separator; since PostgreSQL 14 a negative n counts from the end. SQL Server has no direct equivalent; you need the ordinal column of STRING_SPLIT or some CHARINDEX arithmetic. strpos and its standard spelling position('-' IN order_number) return a 1-based position, or 0 if nothing is found. substring has two faces: the classic cut by numbers and, when given a regex with FROM, the matching part or the first parenthesized group. That is how the copy number in a book title comes out in a single expression. overlay is the standard counterpart of T-SQL's STUFF: it replaces a given number of characters from a given position with another string.

Cleaning and Padding: trim, lpad, initcap, translate

SELECT btrim('  --Dune--  ', ' -') AS btrimmed, trim(BOTH '0' FROM '000420') AS trimmed,
       lpad('42', 8, '0') AS padded, rpad('Dune', 8, '.') || '|' AS rpadded, lpad('Middlemarch', 6) AS lpad_cut;
SELECT initcap('the hobbit, or there and back again') AS initcapped,
       translate('Moby-Dick #8', '-#', ' ') AS translated, repeat('*', 5) AS stars;
SELECT string_to_array('classic,bestseller,,gift', ',') AS arr,
       array_to_string(ARRAY['a', NULL, 'c'], ',', '?') AS joined;

 btrimmed | trimmed |  padded  |  rpadded  | lpad_cut
----------+---------+----------+-----------+----------
 Dune     | 42      | 00000042 | Dune....| | Middle

             initcapped              | translated  | stars
-------------------------------------+-------------+-------
 The Hobbit, Or There And Back Again | Moby Dick 8 | *****

             arr              | joined
------------------------------+--------
 {classic,bestseller,"",gift} | a,?,c

There are three small surprises here. trim removes every character you give it from both ends, not just spaces: the trailing zero of '000420' went too, leaving 42 instead of 420. To drop leading zeros, use ltrim as above. lpad silently cuts a string longer than the target length: "Middlemarch" became "Middle". translate maps character by character and deletes characters that have no counterpart: - became a space, while # vanished because the replacement list has nothing for it. initcap capitalizes the first letter of every word but knows nothing about English title case ("Or", "And"). To split by a separator there are string_to_array and the row-returning string_to_table; both keep the empty field between two separators as an empty string.

A Short Map for T-SQL Developers

format(): Safe Dynamic Text

format is a function similar to C's printf, but it has three specifiers made for SQL: %s inserts the value as plain text, %I as an identifier (a table or column name), %L as a literal (a quoted value).

SELECT format('Order %s for customer %s: %s %s', order_number, customer_id, total_amount, currency) AS line
FROM orders
WHERE order_id = 1042;
SELECT format('SELECT * FROM %I WHERE title = %L', 'Books', 'The Hitchhiker''s Guide') AS safe_sql,
       format('%L', NULL) AS null_literal, format('%s', NULL) AS null_s,
       format('%2$s, %1$s', 'Ada', 'Lovelace') AS positional,
       format('[%-10s][%10s]', 'Dune', 'Emma') AS widths;

                      line
-------------------------------------------------
 Order BK-00001042 for customer 1328: 101.81 TRY

                           safe_sql                            | null_literal | null_s |  positional   |          widths
---------------------------------------------------------------+--------------+--------+---------------+--------------------------
 SELECT * FROM "Books" WHERE title = 'The Hitchhiker''s Guide' | NULL         |        | Lovelace, Ada | [Dune      ][      Emma]

%I put the capitalized "Books" in double quotes (remember the quoted table name trap from post 02), %L doubled the apostrophe and wrote NULL as an unquoted NULL. %s, on the other hand, turns NULL into an empty string. Arguments can also be referenced by position, as in %2$s, and given a width. For a single value, quote_ident, quote_literal and quote_nullable do the same job.

We saw why this matters with a DO block, an anonymous block of code that runs once; its language is PL/pgSQL, which post 22 covers in detail. Dynamic SQL that puts a title coming from the user into the query by string concatenation:

DO $$
DECLARE
    v_title text := 'Dune''; DROP TABLE books; --';
    v_count bigint;
BEGIN
    EXECUTE 'SELECT count(*) FROM books WHERE title = ''' || v_title || '''' INTO v_count;
END
$$;

ERROR:  cannot drop table books because other objects depend on it
DETAIL:  constraint fk_order_items_books on table order_items depends on table books
constraint fk_stock_items_books on table stock_items depends on table books
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT:  SQL statement "SELECT count(*) FROM books WHERE title = 'Dune'; DROP TABLE books; --'"
PL/pgSQL function inline_code_block line 6 at EXECUTE

The error message is not reassuring: DROP TABLE books really ran, and only the foreign keys depending on the table saved it. A table without dependencies would have been dropped. The same query with format, or with USING, which passes the value as a parameter:

DO $$
DECLARE
    v_title text := 'Dune''; DROP TABLE books; --';
    v_count bigint;
BEGIN
    EXECUTE format('SELECT count(*) FROM %I WHERE title = %L', 'books', v_title) INTO v_count;
    RAISE NOTICE 'format: % rows', v_count;
    EXECUTE 'SELECT count(*) FROM books WHERE title = $1' INTO v_count USING v_title;
    RAISE NOTICE 'USING: % rows', v_count;
END
$$;

NOTICE:  format: 0 rows
NOTICE:  USING: 0 rows

Both searched for the value as a single string and found zero rows. The rule is simple: for values, use USING (a parameter) first; where a parameter is not possible (a table or column name), use %I; if you really have to embed a value in the text, use %L. On the application side the same rule holds with Npgsql parameters, as we saw in post 11. The injection post of the Web Güvenliği series covers the topic in general.

LIKE, ILIKE, SIMILAR TO and POSIX Regex

PostgreSQL has four ways to match text. We counted them side by side on the same table:

SELECT count(*) FILTER (WHERE title LIKE 'Dune%') AS like_cnt,
       count(*) FILTER (WHERE title ILIKE 'dune%') AS ilike_cnt,
       count(*) FILTER (WHERE title ~ '^Dune #\d{1,2}$') AS regex_cnt,
       count(*) FILTER (WHERE title ~* '^(dune|emma) ') AS regex_ci_cnt,
       count(*) FILTER (WHERE title SIMILAR TO '(Dune|Emma) #[0-9]{3}') AS similar_cnt
FROM books;

 like_cnt | ilike_cnt | regex_cnt | regex_ci_cnt | similar_cnt
----------+-----------+-----------+--------------+-------------
     2000 |      2000 |         4 |         4000 |          90

LIKE is standard and case-sensitive; ILIKE is PostgreSQL's case-insensitive version. In SQL Server sensitivity comes from the collation; in PostgreSQL the default collations are case-sensitive, so a separate operator is used. ~ and ~* are POSIX regexes, case-sensitive and case-insensitive; !~ and !~* are their negations. The important difference: a regex matches anywhere in the text, so an exact match needs ^ and $. SIMILAR TO is the standard's hybrid syntax between LIKE and regex, and it always matches the whole string: there are 90 copies of Dune and Emma with a three-digit number. In practice SIMILAR TO is rarely needed; either LIKE is enough or you write a regex directly.

The regexp_* Functions

SELECT regexp_match('BK-00001042', '^([A-Z]+)-(\d+)$') AS m,
       (regexp_match('BK-00001042', '^([A-Z]+)-(\d+)$'))[2]::int AS seq;
SELECT regexp_matches('ISBN 978-0-441-17271-9, ISBN 978-0-261-10221-7', '97[89][-0-9]{14}', 'g') AS found;
SELECT regexp_replace('978-0-441-17271-9', '[^0-9]', '') AS first_only,
       regexp_replace('978-0-441-17271-9', '[^0-9]', '', 'g') AS all_digits,
       regexp_replace('Hopper, Grace', '^(\w+), (\w+)$', '\2 \1') AS swapped;
SELECT regexp_count('The Lord of the Rings', 'the', 1, 'i') AS cnt,
       regexp_like('9780441172719', '^97[89]\d{10}$') AS is_isbn,
       regexp_substr('BK-00001042', '\d+') AS sub,
       regexp_instr('Moby-Dick #8', '#') AS pos;
SELECT word, n FROM regexp_split_to_table('The Time  Machine', '\s+') WITH ORDINALITY AS t(word, n);

       m       | seq
---------------+------
 {BK,00001042} | 1042

        found
---------------------
 {978-0-441-17271-9}
 {978-0-261-10221-7}

    first_only    |  all_digits   |   swapped
------------------+---------------+--------------
 9780-441-17271-9 | 9780441172719 | Grace Hopper

 cnt | is_isbn |   sub    | pos
-----+---------+----------+-----
   2 | t       | 00001042 |  11

  word   | n
---------+---
 The     | 1
 Time    | 2
 Machine | 3

regexp_match returns the groups of the first match as a text[]; you take the group you want by array index. regexp_matches with the 'g' flag produces one row per match: the two ISBNs in the text became two rows. The most common mistake with regexp_replace is forgetting 'g': in the first column only the first hyphen was removed. Back references such as \2 \1 let you swap groups. PostgreSQL 15 added regexp_count, regexp_like, regexp_substr and regexp_instr, with the same names as in Oracle and SQL Server 2025; in older code you will see the same jobs written with ~ and substring. regexp_split_to_table splits by a regex; thanks to \s+ the two spaces counted as one separator, and WITH ORDINALITY adds the position of each part.

Bookstore: Validating ISBNs and Generating Slugs

The format of an ISBN-13 is checked with a regex, the check digit with arithmetic: the digits are multiplied by 1 and 3 in turn and summed, and the sum must be divisible by 10.

SELECT isbn,
       isbn ~ '^97[89][0-9]{10}$' AS format_ok,
       CASE WHEN isbn ~ '^97[89][0-9]{10}$' THEN
           (SELECT sum(substr(isbn, i, 1)::int * CASE WHEN i % 2 = 0 THEN 3 ELSE 1 END)
            FROM generate_series(1, 13) AS i) % 10 = 0
       END AS checksum_ok
FROM (VALUES ('9780441172719'), ('9780441172718'), ('978044117271X')) AS v(isbn);
SELECT count(*) AS total,
       count(*) FILTER (WHERE isbn ~ '^97[89][0-9]{10}$') AS format_ok,
       count(*) FILTER (WHERE (SELECT sum(substr(isbn, i, 1)::int * CASE WHEN i % 2 = 0 THEN 3 ELSE 1 END)
                               FROM generate_series(1, 13) AS i) % 10 = 0) AS checksum_ok
FROM books;

     isbn      | format_ok | checksum_ok
---------------+-----------+-------------
 9780441172719 | t         | t
 9780441172718 | t         | f
 978044117271X | f         |

 total | format_ok | checksum_ok
-------+-----------+-------------
 40000 |     40000 |        4000

The number with a changed last digit passed the format check but failed the check digit. The CASE is not decoration: for a value containing "X", substr(...)::int raises invalid input syntax for type integer: "X", so only values with a valid format get to the arithmetic (which is why the check column of the third row is empty). All 40,000 ISBNs in the lab have a valid format, but only 4,000 have a matching check digit: the numbers in the schema are generated with '978' || lpad(n, 10, '0') and are not real ISBNs. In a real catalog this check belongs in a CHECK constraint or a domain; the isbn13 domain from post 03 only checked the format, and in post 22 we will put the check digit into a function and use it in a constraint.

Generating a slug from the title for the book page URL is a chain as well: lowercase with Turkish rules, map Turkish letters to their ASCII counterparts, turn everything that is not a letter or digit into a hyphen, and drop hyphens at both ends.

SELECT title,
       trim(BOTH '-' FROM regexp_replace(
           translate(lower(title COLLATE "tr-TR-x-icu"), 'çğıöşüâî', 'cgiosuai'),
           '[^a-z0-9]+', '-', 'g')) AS slug
FROM (VALUES ('Çalıkuşu'), ('İnce Memed'), ('Kürk Mantolu Madonna'), ('The Hobbit #2'),
             ('Moby-Dick, or The Whale')) AS v(title);

          title          |          slug
-------------------------+------------------------
 Çalıkuşu                | calikusu
 İnce Memed              | ince-memed
 Kürk Mantolu Madonna    | kurk-mantolu-madonna
 The Hobbit #2           | the-hobbit-2
 Moby-Dick, or The Whale | moby-dick-or-the-whale

We also added the circumflexed letters (â, î) to the list; it is enough for Turkish, but if letters such as the German ß show up, the unaccent extension from post 10 is a more general solution.

to_char and to_number: Formatting Numbers

SHOW lc_numeric;
SELECT to_char(1234.5, '999G999D99') AS padded, to_char(1234.5, 'FM999G999D00') AS fm,
       to_char(1234567.5, 'FM999G999D00') AS too_big, to_char(42, 'FM00000') AS zeros,
       to_char(0.256, 'FM990.0%') AS pct;

 lc_numeric
------------
 en_US.utf8

   padded    |    fm    |  too_big   | zeros | pct
-------------+----------+------------+-------+------
    1,234.50 | 1,234.50 | ###,###.## | 00042 | 0.3%

In the pattern, 9 is an optional digit, 0 a required digit, G the group separator and D the decimal separator. The FM prefix turns off padding; without it the result has a fixed width with leading spaces, and room is kept for a sign. A number that does not fit the pattern does not raise an error, it is filled with #: write the pattern for the largest value. % is just a character; it did not turn 0.256 into a percentage, you have to multiply by 100 yourself. G and D take their characters from the lc_numeric setting. We tried changing it to get the Turkish format:

SET lc_numeric = 'tr_TR.UTF-8';
SELECT translate(to_char(1234.5, 'FM999G999G990D00'), ',.', '.,') || ' TL' AS tr_price;
SELECT to_number('1.234,50', 'FM9G999D99') AS parsed_as_en,
       to_number(translate('1.234,50', '.,', ',.'), 'FM9G999D99') AS parsed_as_tr;

ERROR:  invalid value for parameter "lc_numeric": "tr_TR.UTF-8"

  tr_price
-------------
 1.234,50 TL

 parsed_as_en | parsed_as_tr
--------------+--------------
         1.23 |       1234.5

The official Docker image only has the en_US.utf8, C and POSIX libc locales (locale -a), so lc_numeric cannot be switched to Turkish. The most portable solution is to swap the separators with translate. The real danger is in the other direction: reading the Turkish "1.234,50" with English rules, to_number did not fail, it returned 1.23. Parsing user input in the application with CultureInfo and sending it as a parameter is safer than parsing it in the database. Bookstore's monthly TRY revenue report uses this approach:

SELECT to_char(created_at, 'YYYY-MM') AS month,
       translate(to_char(sum(total_amount), 'FM999G999G990D00'), ',.', '.,') || ' TL' AS revenue
FROM orders
WHERE currency = 'TRY' AND created_at >= '2026-08-01' AND created_at < '2026-10-01'
GROUP BY 1
ORDER BY 1;

  month  |     revenue
---------+-----------------
 2026-08 | 1.203.586,29 TL
 2026-09 | 1.144.863,78 TL

The date patterns of to_char (YYYY-MM, TMMonth) and how the month boundaries in this query relate to time zones are the subject of the next post.

Turkish Letters: Default Collation and ICU

In post 03 we saw how upper and lower treat the Turkish i/İ and looked at nondeterministic collations. The same problem shows up in initcap and ILIKE:

SELECT initcap('istanbul ırmak') AS default_initcap,
       initcap('istanbul ırmak' COLLATE "tr-TR-x-icu") AS tr_initcap,
       lower('KIRMIZI İNCİ') AS default_lower,
       lower('KIRMIZI İNCİ' COLLATE "tr-TR-x-icu") AS tr_lower;
SELECT 'IŞIK' ILIKE 'ışık' AS default_ilike, 'IŞIK' COLLATE "tr-TR-x-icu" ILIKE 'ışık' AS tr_ilike;

 default_initcap |   tr_initcap   | default_lower |   tr_lower
-----------------+----------------+---------------+--------------
 Istanbul Irmak  | İstanbul Irmak | kirmizi inci  | kırmızı inci

 default_ilike | tr_ilike
---------------+----------
 f             | t

The default collation of the lab database is en_US.utf8: it turns KIRMIZI into kirmizi and treats "IŞIK" and "ışık" as different. With tr-TR-x-icu both are right. You can attach COLLATE to an expression, a column or the database; when you put it on a column, the column's index is ordered by those rules too. For case-insensitive equality there is also the citext extension (post 13), but the PostgreSQL documentation now recommends nondeterministic collations; citext always lowercases with the database's rules, so it does not solve the Turkish I/ı problem.

Indexes and LIKE: text_pattern_ops and pg_trgm

Someone coming from SQL Server expects that if there is an index on title, LIKE 'Dune #12%' uses it. In PostgreSQL that depends on the collation:

CREATE INDEX ix_books_title ON books (title);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title = 'Dune #120';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title LIKE 'Dune #12%';

                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using ix_books_title on books (actual rows=1.00 loops=1)
   Index Cond: (title = 'Dune #120'::text)
   Index Searches: 1
   Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=16 read=1

                  QUERY PLAN
-----------------------------------------------
 Seq Scan on books (actual rows=56.00 loops=1)
   Filter: (title ~~ 'Dune #12%'::text)
   Rows Removed by Filter: 39944
   Buffers: shared hit=444
 Planning:
   Buffers: shared hit=5

Equality used the index, the prefix search did not. With a collation such as en_US.utf8, sorting follows language rules, so the order in the B-tree does not line up with the byte-wise "starts with" range. The fix is a second index with an operator class that compares characters in byte order:

CREATE INDEX ix_books_title_pattern ON books (title text_pattern_ops);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title LIKE 'Dune #12%';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title ILIKE '%hobbit #12%';
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_books_title_trgm ON books USING gin (title gin_trgm_ops);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title ILIKE '%hobbit #12%';

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using ix_books_title_pattern on books (actual rows=56.00 loops=1)
   Index Cond: ((title ~>=~ 'Dune #12'::text) AND (title ~<~ 'Dune #13'::text))
   Filter: (title ~~ 'Dune #12%'::text)
   Index Searches: 1
   Buffers: shared hit=21 read=4
 Planning:
   Buffers: shared hit=63 read=1

                  QUERY PLAN
-----------------------------------------------
 Seq Scan on books (actual rows=56.00 loops=1)
   Filter: (title ~~* '%hobbit #12%'::text)
   Rows Removed by Filter: 39944
   Buffers: shared hit=444
 Planning:
   Buffers: shared hit=4

                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on books (actual rows=56.00 loops=1)
   Recheck Cond: (title ~~* '%hobbit #12%'::text)
   Heap Blocks: exact=14
   Buffers: shared hit=31
   ->  Bitmap Index Scan on ix_books_title_trgm (actual rows=56.00 loops=1)
         Index Cond: (title ~~* '%hobbit #12%'::text)
         Index Searches: 1
         Buffers: shared hit=17
 Planning:
   Buffers: shared hit=25

The text_pattern_ops index turned LIKE into two comparisons and read 25 buffers instead of 444. This index only helps prefix searches; ORDER BY title and ordinary comparisons still need the first index. If the database was created with the C collation, one index serves both. A case-insensitive search in the middle of the text (ILIKE '%...%') never gets faster with a B-tree; pg_trgm's GIN index found the candidates by trigrams and finished in 31 buffers. We looked at pg_trgm's similarity search and its cost in posts 06 and 10. The same index can also speed up ~ and ~* regex searches.

In Production

For every pattern you search text columns with, check with EXPLAIN which index is used: equality is served by a normal B-tree, prefixes by text_pattern_ops or a C collation, infix searches and regexes by a trigram GIN index. A GIN index has a write cost; on a 40,000-row title column it does not matter, on a large, frequently updated table measure it.

Make the collation a decision in one place. If the screen needs Turkish sorting and initcap, use ICU tr-TR on the column or database; for language-neutral columns such as e-mail, codes and ISBNs, C or a builtin collation is more predictable. In post 03 we saw that indexes have to be rebuilt when the collation version changes.

Leave formatting to the application wherever you can. to_char is handy in report queries and psql, but numbers returned by the API should stay numbers; let the client produce "1.234,50 TL" with its own culture.

Common Pitfalls

Concatenating NULL with ||: a single empty column turns the whole line into NULL. In queries that produce display text, use concat_ws or coalesce.

Writing dynamic SQL by string concatenation: the DROP TABLE above really ran. USING for values, %I for names.

Forgetting 'g' in regexp_replace, and overlooking that a regex also matches in the middle of the text: validation regexes should start with ^ and end with $.

Trying to remove leading zeros with trim(BOTH '0' ...): the trailing ones go too. Use ltrim or convert to a number.

Assuming the default collation is Turkish: with en_US, "IŞIK" and "ışık" are different and the lowercase of KIRMIZI is kirmizi. Turkish rules only come with the ICU tr-TR collation.

Expecting a B-tree index to speed up ILIKE '%x%': because of the leading %, no B-tree can be used; you need a trigram index or the full-text search from post 10.

A Small Experiment

★☆☆ Extract the customer number from the e-mail addresses in customers (customer42@example.com) in three ways: split_part + replace, substring ... FROM and regexp_substr. Verify that all three give the same result by comparing with customer_id.

★★☆ Add the slug expression as a generated column to a copy of books and check whether any books share a slug. Then try to put a unique index on the slug: what happens on 18 when you do not write STORED? Guess first, remembering post 16.

★★★ Look at the plan of title ~* '^the (hobbit|silmarillion) #1\d{3}$' with the ix_books_title_trgm index. Then rewrite the same search as two LIKE conditions that the text_pattern_ops index can speed up, and compare the buffer counts.

In the next post we move on to dates and times: the difference between now() and clock_timestamp(), date_trunc, AT TIME ZONE, interval arithmetic and filling empty days with generate_series.