PostgreSQL vs MariaDB: the Differences That Actually Bite
Both speak SQL, and for simple queries they are interchangeable. The friction appears in a handful of everyday operations, and each has a one-line portable answer.
Case-insensitive search
PostgreSQL has ILIKE: WHERE name ILIKE '%smith%'. MariaDB has no ILIKE; its default collation is already case-insensitive, so LIKE behaves that way.
The portable form is WHERE LOWER(name) LIKE '%smith%', which works on both. It costs you an index unless you add a functional index, which is the honest price of portability.
String concatenation
PostgreSQL uses ||: first_name || ' ' || last_name. MariaDB treats || as logical OR unless PIPES_AS_CONCAT is enabled, so the same expression silently returns 0 or 1.
Use CONCAT(first_name, ' ', last_name), which works in both and cannot be misread.
LIMIT and OFFSET
Both accept LIMIT 10 OFFSET 20. MariaDB also accepts the LIMIT 20, 10 shorthand (offset first); PostgreSQL does not. If a query must run on both, always write LIMIT n OFFSET m — the comma form is the one that breaks.
RETURNING
PostgreSQL returns inserted or updated rows directly: INSERT ... RETURNING id. MariaDB 10.5 and later supports RETURNING for INSERT and DELETE, but not for UPDATE. Where it is unavailable, the portable pattern is a follow-up SELECT — or in MariaDB, LAST_INSERT_ID().
Booleans and types
PostgreSQL has a real boolean type; MariaDB stores booleans as TINYINT(1) with 0 and 1. A query comparing to true works in PostgreSQL and is emulated in MariaDB, but a value of 2 is truthy in MariaDB and impossible in a PostgreSQL boolean column. Do not rely on "anything non-zero" if you may move between them.