PostgreSQL String Functions

Calling ascii() in a loop or for each character to check whether a string is pure ASCII or contains only certain characters is slow and verbose. A single `regexp_replace` or `translate` call handles the whole string in one pass.

✓ Instead: Use `col ~ '[^\x20-\x7E]'` to detect non-ASCII characters, or `regexp_replace(col, '[^\x20-\x7E]', '', 'g')` to strip them — no loop needed.

Developers sometimes use `bit_length(col) / 8` to compute a byte count and enforce storage limits. This is needlessly indirect. `octet_length(col)` returns bytes directly and is the correct tool for enforcing byte-level constraints such as network protocol limits or column storage checks.

✓ Instead: Use `CHECK (octet_length(col) <= 1024)` instead of `CHECK (bit_length(col) / 8 <= 1024)`.

Using `WHERE btrim(col) = 'value'` prevents index use — PostgreSQL cannot use a plain B-tree index on `col` when a function wraps it. Every row is scanned and trimmed at runtime.

✓ Instead: Create a functional index `CREATE INDEX ON t (btrim(col))` and query with `WHERE btrim(col) = 'value'`, or better yet store pre-trimmed values by applying btrim at write time via a CHECK constraint or trigger.

A VARCHAR(255) limit on a column is a character limit, but external systems such as Kafka, HTTP headers, or SMS gateways have byte limits. `char_length('emoji😀') = 6` but `octet_length('emoji😀') = 9`. Validating against char_length will let oversized payloads through when users include emoji or CJK characters.

✓ Instead: Use `octet_length(col) <= N` for byte-budget checks, and `char_length(col) <= N` only for display/UX character counts.

Pasting literal newline or tab characters into a SQL string literal makes the query unreadable and fragile across editors and terminals that may silently strip or convert whitespace.

✓ Instead: Use `chr(10)` for LF, `chr(13)` for CR, and `chr(9)` for tab. For CRLF: `chr(13) || chr(10)`. This keeps SQL scripts readable and encoding-safe.

A very common bug: `first_name || ' ' || last_name` returns NULL whenever either column is NULL. Developers who expect this to 'just work' like concat() get NULL full names for users with no last name on record.

✓ Instead: Use `concat(first_name, ' ', last_name)` or `concat_ws(' ', first_name, last_name)` when any input may be NULL.

`concat_ws(', ', 'a', '', 'c')` returns `'a, , c'` — the empty string produces a dangling separator, which surprises developers who expect empty strings to be skipped like NULLs.

✓ Instead: Use `NULLIF(col, '')` to coerce empty strings to NULL before passing to concat_ws: `concat_ws(', ', NULLIF(line2,''), NULLIF(city,''))`.

Writing `col::text` on a bytea column that holds Latin-1 or Windows-1252 bytes silently misinterprets the data — PostgreSQL assumes UTF-8. Characters above U+007F will either corrupt or raise a conversion error.

✓ Instead: Always use `convert_from(col, 'WIN1252')` (or the correct source encoding) so PostgreSQL performs a proper encoding conversion to the database's UTF-8 encoding.

Storing images or file content as base64 text and calling decode() on every read adds CPU overhead for decoding and bloats storage by ~33% compared to native bytea. It also makes byte-level operations (like checking file magic bytes) require an extra decode step.

✓ Instead: Store binary data in a `bytea` column directly. Use `decode()` only as a one-time migration step to convert existing base64 text into bytea.

PostgreSQL's base64 output inserts newline characters every 76 characters per the MIME standard. Embedding this directly in a URL or JSON field produces broken tokens.

✓ Instead: Strip newlines after encoding: `replace(encode(data, 'base64'), chr(10), '')`. For URL-safe base64, also translate: `translate(replace(encode(data,'base64'), chr(10),''), '+/=', '-_')`.

Writing `'SELECT * FROM ' || table_name || ' WHERE id = ' || user_id` in a PL/pgSQL function is a SQL injection vector when `table_name` or `user_id` come from user input or external data. It also becomes unreadable with many substitutions.

✓ Instead: Use `format('SELECT * FROM %I WHERE id = %L', table_name, user_id)`. The `%I` specifier safely quotes identifiers; `%L` safely quotes literals.

`initcap('mcdonald')` returns `Mcdonald` (not `McDonald`), and `initcap('macleod')` returns `Macleod` (not `MacLeod`). Using initcap as a general name-normalization function in a customer database will silently produce incorrect titles for a meaningful fraction of names.

✓ Instead: Use initcap only for generic title-casing (product names, city names). For personal names, implement a custom normalization function or handle it in application code where you can manage special-case rules.

`WHERE left(col, 5) = 'ACME-'` wraps the column in a function call, preventing B-tree index use on `col`. The query does a full sequential scan.

✓ Instead: Use `WHERE col LIKE 'ACME-%'` or `WHERE starts_with(col, 'ACME-')` — both can use a standard B-tree index on the column.

`WHERE length(col) > 0` silently excludes NULL rows without error — they just disappear from results. Developers often write this expecting it to mean 'has any value' but NULLs are not matched.

✓ Instead: Write `WHERE col IS NOT NULL AND col <> ''` to explicitly handle both empty strings and NULLs, making the intent clear.

`WHERE lower(email) = 'user@example.com'` performs a full table scan on large tables — PostgreSQL cannot use a plain B-tree index on `email` when the column is wrapped in lower().

✓ Instead: Create a functional index: `CREATE INDEX ON users (lower(email))` and keep the WHERE clause as `WHERE lower(email) = $1`. Alternatively, use the `citext` extension for transparent case-insensitive comparisons without function calls.

If different rows are padded to different widths (e.g. some to 6 digits, others to 8), lexicographic ORDER BY still produces wrong results. lpad only fixes sort order when ALL values are padded to the same target length.

✓ Instead: Always use a fixed, consistent length for lpad: `lpad(id::text, 10, '0')` for all rows. If you store the padded value, add a CHECK constraint ensuring the length is always exactly N.

`ltrim('000', '0')::integer` throws an error because ltrim returns an empty string, and casting '' to integer fails with `invalid input syntax for type integer`.

✓ Instead: Use `NULLIF(ltrim(col, '0'), '')::integer` — the NULLIF converts empty string to NULL, and NULL::integer is NULL rather than an error.

MD5 is cryptographically broken. Using `md5(password)` or even `md5(salt || password)` for password storage is dangerously insecure — rainbow tables and GPU cracking render it useless. This is a well-known but still common mistake.

✓ Instead: Use the `pgcrypto` extension: `crypt(password, gen_salt('bf', 12))` for bcrypt hashing, and `crypt(input, stored_hash)` for verification. Never store MD5-hashed passwords.

PostgreSQL's `VARCHAR(n)` limit is in characters, not bytes. But external systems (Kafka, S3 metadata, SNS) often have byte limits. A 255-character field containing 255 emoji is 1,020 bytes — far over a 1KB limit. Checking `char_length(col) <= 255` does not catch this.

✓ Instead: Add a separate CHECK constraint: `CHECK (octet_length(col) <= 1024)` when the destination system has a byte budget.

When FOR is omitted, overlay replaces exactly `length(replacement)` characters. If your replacement is shorter or longer than the section you want to overwrite, you get unexpected results — characters are shifted rather than replaced in-place.

✓ Instead: Always specify FOR explicitly: `overlay(col PLACING new_val FROM start FOR length_to_remove)`. Count the characters you want to overwrite, not the length of the replacement.

In Python and JavaScript, `str.indexOf(sub) > -1` or `str.find(sub) != -1` tests for presence. In PostgreSQL, `strpos` returns 0 (not -1) for no match — so `strpos(col, 'foo') > -1` is always true, matching every row including those without the substring.

✓ Instead: Use `strpos(col, 'foo') > 0` or simply `col LIKE '%foo%'` to test for substring presence.

Writing `'SELECT * FROM ' || schema_name || '.' || table_name` in a stored procedure is a SQL injection risk. If either value contains special characters or reserved words, the query will either fail or — with malicious input — execute unintended SQL.

✓ Instead: Use `format('SELECT * FROM %I.%I', schema_name, table_name)` which calls quote_ident internally and handles all edge cases safely.

`'SET col = ' || quote_literal(maybe_null)` produces NULL when `maybe_null` is NULL — and `EXECUTE NULL` raises an error or does nothing. This is a silent failure that only appears at runtime when a nullable column is updated.

✓ Instead: Use `quote_nullable(maybe_null)` which returns the unquoted string `NULL` for NULL input, producing valid SQL like `SET col = NULL`.

Many developers default to `quote_literal` for all value substitution in dynamic SQL because it's better known. But any nullable column will break the generated SQL when the value is NULL — `quote_literal(NULL)` propagates NULL into the string concatenation.

✓ Instead: Make `quote_nullable` your default for all value quoting in PL/pgSQL. Reserve `quote_literal` for cases where a NULL value is genuinely an error condition.

`regexp_count` was added in PostgreSQL 15. Calling it on PG 14 or earlier raises `ERROR: function regexp_count(text, text) does not exist`. Applications that need to support multiple PG versions will break at runtime.

✓ Instead: Check your minimum supported PostgreSQL version. For pre-PG15, use the workaround: `array_length(regexp_split_to_array(col, pattern), 1) - 1` or a custom PL/pgSQL helper.

Using `WHERE regexp_like(email, '^[^@]+@[^@]+\.[a-z]{2,}

✓ Instead: For email columns, use a permissive CHECK like `CHECK (email LIKE '%@%.%')` combined with application-layer validation, or use the `pg_trgm` extension for fuzzy matching. Reserve regexp_like for filtering and data auditing queries.

`(regexp_match(col, 'pattern'))[1]` silently returns NULL when there is no match — which is fine in a SELECT, but causes silent data loss in an INSERT or UPDATE where you expected a value.

✓ Instead: Use `COALESCE((regexp_match(col, pattern))[1], '')` or add a WHERE filter: `WHERE regexp_match(col, pattern) IS NOT NULL` before relying on the result.

`regexp_replace(col, '[^a-zA-Z]', '', )` without the 'g' flag removes only the first non-letter character. Rows with multiple non-letter characters appear 'cleaned' but still contain unwanted characters — a bug that is easy to miss in testing on simple data.

✓ Instead: Always add the 'g' flag when you want all matches replaced: `regexp_replace(col, '[^a-zA-Z]', '', 'g')`.

Using `regexp_split_to_array(col, ',')` when the delimiter is a plain literal character adds regex overhead without benefit. Special regex characters in the delimiter also need escaping, which is easy to forget.

✓ Instead: Use `string_to_array(col, ',')` for literal delimiters — it is faster, cleaner, and the delimiter is treated as a plain string, not a pattern.

Running `regexp_split_to_table(col, pattern)` per row in a correlated subquery or via a LATERAL join on millions of rows can become a significant bottleneck since the regex is compiled and applied row by row.

✓ Instead: Process text splitting in batch: unnest into a staging table first, or use `string_to_array` with `unnest` when the delimiter is a plain literal. For heavy text analysis, consider a dedicated full-text search pipeline.

`repeat('x', n)` where `n` comes from user input with no validation can allocate gigabytes of memory and crash the server. `repeat('x', 1000000000)` will attempt to allocate ~1GB.

✓ Instead: Always cap the repeat count: `repeat('x', LEAST(n, 10000))` or add a CHECK constraint or application-layer validation before passing user input to repeat().

`replace(replace(replace(col, 'é', 'e'), 'ñ', 'n'), 'ü', 'u')` applies three full passes over potentially large strings. As the number of substitutions grows, performance degrades linearly.

✓ Instead: Use `translate(col, 'éñü', 'enu')` for single-character substitutions — one pass regardless of how many character mappings. For substring replacements (length > 1), use `regexp_replace` with alternation: `regexp_replace(col, 'foo|bar', '', 'g')`.

`WHERE reverse(col) LIKE 'moc.elpmaxe.%'` can use an index on `reverse(col)`, but developers often write this in ad-hoc queries without creating the functional index, resulting in a full sequential scan.

✓ Instead: Create `CREATE INDEX ON domains (reverse(domain))` before using `WHERE reverse(domain) LIKE reverse('%.' || $1)` in production queries. Without the index, reverse-based suffix matching is no faster than `col LIKE '%suffix'`.

`WHERE right(col, 4) = '.csv'` wraps the column in a function call, which prevents B-tree index use. Every row is evaluated at runtime.

✓ Instead: Use `WHERE col LIKE '%.csv'` — while LIKE with a leading wildcard also can't use a B-tree index, it avoids an unnecessary function call. For indexed suffix searches, use a functional index on `reverse(col)` and query `WHERE reverse(col) LIKE reverse('%.csv')`.

`rpad('toolongstring', 5)` returns `'toolon'` — data is silently truncated, not an error. When generating fixed-width export files, any value longer than the field width will be cut off without warning, corrupting the record layout.

✓ Instead: Add an assertion or validation step before rpad in ETL pipelines: `WHERE length(col) <= 20` or raise an exception in PL/pgSQL when any value exceeds the column width.

`rtrim('3.14000', '0')` returns `'3.14'` as expected, but `rtrim('100', '0')` returns `'1'` — stripping significant zeros too. The characters argument is a set, not a suffix, so it removes any trailing character that is in the set.

✓ Instead: For stripping trailing decimal zeros from numeric strings, cast to numeric and back: `trim(trailing '0' from col::numeric::text)` is safer, or use `to_char(val::numeric, 'FM999999990.##########')` for formatting.

`split_part('"Smith, John",30', ',', 1)` returns `'"Smith'` — not `'"Smith, John"'`. split_part does not understand CSV quoting rules and splits on every literal delimiter character.

✓ Instead: For proper CSV parsing with quoted fields, use PostgreSQL's COPY command, the `csv_fdw` extension, or parse in application code. split_part is safe only for delimiter-separated values where the delimiter cannot appear in field values.

`starts_with(col, 'IMG')` does not match rows where col starts with 'img' or 'Img'. This is easy to miss when data comes from mixed-case sources.

✓ Instead: For case-insensitive prefix checks: `starts_with(lower(col), lower('IMG'))` or `col ILIKE 'IMG%'`. Create a functional index on `lower(col)` if performance matters.

If you routinely split a text column with string_to_array to use its elements — filtering, joining, or unnesting — you've found a schema design problem. Splitting on every query adds overhead and prevents efficient GIN indexing.

✓ Instead: Migrate the column to a native `text[]` array type. Use GIN indexes for containment and overlap queries (`&&`, `@>`). The data becomes a first-class array, queryable without parsing on every access.

`string_to_table` was added in PostgreSQL 14. Using it without a version check will raise `ERROR: function string_to_table(text, text) does not exist` on older servers, which is a runtime error that only surfaces in production.

✓ Instead: For pre-PG14 compatibility, use `unnest(string_to_array(col, ','))` which works from PG 9.0+. Add a comment noting when you can drop the workaround once your minimum supported version is PG 14.

`substr('hello', 0, 4)` returns `'hel'` — 3 characters, not 4. Position 0 is treated as 1, but the count is also adjusted, so you get one fewer character than you might expect. This off-by-one trips up developers who port code from languages where string slicing is 0-indexed.

✓ Instead: Always use 1-based positions: `substr(col, 1, 3)` clearly returns the first 3 characters. Or use `left(col, 3)` which is unambiguous.

`substring('2024-03-15' FROM '(\d{4})-(\d{2})-(\d{2})')` returns only `'2024'` — the first capture group. Developers expecting all three groups are returned get only the year and silently lose month and day.

✓ Instead: Use `regexp_match('2024-03-15', '(\d{4})-(\d{2})-(\d{2})')` which returns `{2024,03,15}` — all capture groups as an array.

`to_hex(255)` = `'ff'` (2 chars) but `to_hex(256)` = `'100'` (3 chars). Storing or comparing raw to_hex output as identifiers or sort keys produces incorrect lexicographic ordering and mismatched string lengths.

✓ Instead: Always pad to a consistent width: `lpad(to_hex(val), 8, '0')` for 32-bit values, or `lpad(to_hex(val), 16, '0')` for 64-bit. This ensures correct string comparison and fixed-width output.

`translate('hello', 'el', 'ip')` returns `'hippo'` — it maps 'e'→'i' and 'l'→'p' character by character. Developers expecting `'el'` to be treated as a literal substring (like replace) are surprised when individual 'e' and 'l' characters are substituted throughout the string.

✓ Instead: Use `replace(col, 'from_substring', 'to_substring')` when you need to substitute a multi-character literal. Use `translate` only for character-to-character one-for-one mapping.

Wrapping every query with `WHERE trim(col) = $1` or `SELECT trim(col)` is wasteful — the trim executes on every read across all rows. If the column always stores untrimmed data, every consumer must remember to trim.

✓ Instead: Enforce trimming at write time: add a CHECK constraint `CHECK (col = trim(col))` or a BEFORE INSERT/UPDATE trigger that normalizes the value. The column then always stores clean data and queries need no wrapper.

`WHERE upper(status) = 'ACTIVE'` prevents index use on `status` and runs upper() on every row in the table on every query. If `status` is supposed to be uppercase, callers should not be responsible for normalizing it at read time.

✓ Instead: Store status values in a consistent case enforced at write time (`CHECK (status = upper(status))`). Then `WHERE status = 'ACTIVE'` uses a plain B-tree index with no function call overhead.

`unistr('\u20AC')` does NOT work — the format requires `\20AC` (no `u`). Developers copy-pasting escape sequences from JavaScript (`\u20AC`) or Python (`\u20ac`) into unistr get an error or wrong output.

✓ Instead: Use `unistr('\20AC')` for BMP characters (U+0000 to U+FFFF) and `unistr('\+01F600')` for supplementary characters (above U+FFFF). No `u` prefix — just the bare hex digits after the backslash.

Two strings that look visually identical — such as 'café' composed two different ways — will not compare equal in PostgreSQL if they use different Unicode normalization forms. `'café' = 'café'` can return false when one uses U+00E9 and the other uses e + U+0301.

✓ Instead: Normalize at write time so all stored values use the same form: `CHECK (col = normalize(col, NFC))`. Then comparisons are reliable without normalizing at query time.

`split_part('"schema.with.dots".table', '.', 1)` returns `'"schema'` — it splits on every dot, including dots inside quoted identifier names. Any schema or table name containing a dot will be split incorrectly.

✓ Instead: Use `parse_ident(qualified_name)` which correctly handles quoting: `parse_ident('"schema.with.dots".table')[1]` returns `'schema.with.dots'` as intended.

`regexp_substr` was added in PostgreSQL 15. Using it on PG 14 or earlier raises `ERROR: function regexp_substr(text, text) does not exist` at runtime — not at deploy time, so it may only surface in production when the code path is hit.

✓ Instead: For pre-PG15 compatibility, use `(regexp_match(col, pattern))[1]` for first-match extraction. For Nth-match extraction, use `regexp_matches` with a row_number() CTE. Document the PG15+ requirement clearly if you choose to use regexp_substr.