PostgreSQL vs MySQL: String Functions Compared

The first team I saw move from MySQL to PostgreSQL expected the infrastructure to be the hard part — the new server, the migration tooling, the deployment changes. They were wrong. The infrastructure was fine. The string functions were the problem. Not the syntax, mostly. The behaviour.

String concatenation and the NULL problem

MySQL's CONCAT() returns NULL if any argument is NULL. PostgreSQL's concat() function skips NULLs entirely. But PostgreSQL's || operator propagates NULL, just like MySQL's CONCAT.

-- MySQL
SELECT CONCAT('Hello', NULL, 'World');  -- Returns NULL

-- PostgreSQL
SELECT concat('Hello', NULL, 'World');  -- Returns 'HelloWorld'
SELECT 'Hello' || NULL || 'World';      -- Returns NULL

If you're migrating MySQL CONCAT calls, map them to PostgreSQL concat() if you want nulls skipped, or keep || if you want null-propagation. Use concat_ws() for anything with optional parts.

Case sensitivity: the one that breaks queries silently

MySQL string comparisons are case-insensitive by default. PostgreSQL is always case-sensitive.

-- MySQL (with default collation): returns rows
SELECT * FROM users WHERE email = 'USER@EXAMPLE.COM';

-- PostgreSQL: returns nothing if the email is stored as 'user@example.com'
SELECT * FROM users WHERE email = 'USER@EXAMPLE.COM';

This is silent. No error. Queries just return fewer rows than expected. I've seen this go undetected in a migrated application for two weeks because the test suite used lowercase test data.

In PostgreSQL, use ILIKE or lower() for case-insensitive matching:

SELECT * FROM users WHERE lower(email) = lower('USER@EXAMPLE.COM');
-- or
SELECT * FROM users WHERE email ILIKE 'user@example.com';

For indexed lookups, add a functional index so the query stays fast:

CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

Regular expressions

MySQL uses REGEXP (or RLIKE). PostgreSQL uses POSIX operators:

-- MySQL
SELECT * FROM products WHERE name REGEXP '^[A-Z]';

-- PostgreSQL
SELECT * FROM products WHERE name ~ '^[A-Z]';      -- case-sensitive
SELECT * FROM products WHERE name ~* '^[a-z]';     -- case-insensitive
SELECT * FROM products WHERE name !~ '^[A-Z]';     -- NOT matching

PostgreSQL's regex support is more expressive — lookaheads, named groups, full POSIX syntax. MySQL's REGEXP is basic by comparison.

GROUP_CONCAT vs string_agg

-- MySQL
SELECT project_id,
  GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR ', ') AS tags
FROM project_tags
GROUP BY project_id;

-- PostgreSQL
SELECT project_id,
  string_agg(tag_name, ', ' ORDER BY tag_name) AS tags
FROM project_tags
GROUP BY project_id;

Different syntax, same result. MySQL also has a default 1024-byte length limit on GROUP_CONCAT output. PostgreSQL's string_agg has no such limit.

IFNULL vs COALESCE

-- MySQL
SELECT IFNULL(shipping_address, billing_address) FROM orders;

-- PostgreSQL
SELECT COALESCE(shipping_address, billing_address) FROM orders;

Simple one-to-one replacement. COALESCE accepts any number of arguments; IFNULL is strictly two.

Conversion cheatsheet

MySQL	PostgreSQL	Notes
CONCAT(a, b)	concat(a, b)	PG concat() skips NULLs; || propagates them
IFNULL(a, b)	COALESCE(a, b)	Identical for 2 args
GROUP_CONCAT(x SEPARATOR ',')	string_agg(x, ',')	Add ORDER BY inside aggregate
SUBSTRING(s, 1, 10)	substr(s, 1, 10)	Identical
s REGEXP '^[A-Z]'	s ~ '^[A-Z]'	PG uses POSIX operators
LCASE(s) / UCASE(s)	lower(s) / upper(s)	LCASE/UCASE don't exist in PG
LENGTH(s)	length(s)	Identical
INSTR(str, sub)	strpos(str, sub)	Same behaviour
LEFT(s, n)	left(s, n)	Identical
RIGHT(s, n)	right(s, n)	Identical
REPEAT(s, n)	repeat(s, n)	Identical
TRIM(s)	trim(s)	Identical for whitespace

The one that will definitely break your migration: case sensitivity

This deserves more space, because it doesn't produce an error. It fails silently, in production, weeks after the migration is declared done.

Your test data was probably entered in lowercase. Production data, entered by real users over years, has a mix. A user whose email was stored as Alice@Example.com can't log in after the migration — your MySQL query matched it case-insensitively, your PostgreSQL query doesn't.

The full fix: normalise emails to lowercase at insert time with a CHECK constraint or application-layer validation. For existing data and future queries, use a functional index:

CREATE INDEX idx_users_email_ci ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

Without the functional index, lower(email) = lower($1) does a sequential scan. With it, you get an index scan. Check your query plan to confirm it's being used.

PostgreSQL's strictness here actually saved a different team from a bug they'd had in MySQL for years: two users with emails Admin@company.com and admin@company.com were conflated as the same account. In PostgreSQL, they're distinct. That's correct behaviour, even if it's initially inconvenient to migrate to.