function ifnull(text, unknown) does not exist
Laravel app, just moved from MySQL to PostgreSQL. The full message as Laravel shows it:
SQLSTATE[42883]: Undefined function: 7 ERROR: function ifnull(text, unknown) does not exist
Why it happens
A MySQL-only function in raw SQL. Common ones and their Postgres versions are below.
Fix
IFNULL(a, b) -> COALESCE(a, b)
GROUP_CONCAT(email) -> string_agg(email, ',' ORDER BY id)
DATE_FORMAT(d, '%Y') -> to_char(d, 'YYYY')
IF(cond, a, b) -> CASE WHEN cond THEN a ELSE b END
UNIX_TIMESTAMP(d) -> extract(epoch FROM d)
CURDATE() -> current_date
COALESCE, string_agg and to_char tested on Postgres 17.11.
More fixes like this one, as tested scripts: Laravel Postgres Error Fix Pack ($19).
Several errors at once? Paste your log into the error matcher. Raw SQL with MySQL syntax: the free SQL translator. Not migrated yet: the free schema risk check finds this kind of problem before the switch. Planning the whole move: Migration Kit ($49, $34.30 with LAUNCH30 until Nov 5).
Other errors after MySQL to Postgres
duplicate key value violates unique constraint "users_pkey"column "users.name" must appear in the GROUP BY clause or be used in an aggregate functionvalue "3000000000" is out of range for type integeroperator does not exist: boolean = integeroperator does not exist: character varying = integerdate/time field value out of range: "0000-00-00 00:00:00"column "a@x.com" does not existsyntax error at or near "`"invalid byte sequence for encoding "UTF8": 0x00current transaction is aborted, commands ignored until end of transaction block(no error) search or login finds nothing: WHERE email LIKE ... returns 0 rows