SQL Operators That Exist on Some Engines Only and What to Write Instead

Operators such as ::, ILIKE, DIV and XOR belong to one or two engines, and the others refuse them. BareSQL Portable knows 16 operators like these.

Each operator in the table was used on all four engines, and the table shows where it ran and what to write instead.

SELECT 'abc' ILIKE 'ABC', run on four engines: SQLite 3.45.1 error: near "'ABC'": syntax error; PostgreSQL 16.15 true; MySQL 8.0.46 error: You have an error in your SQL syntax ... near ''ABC'' at line 1; MariaDB 10.11.14 error: You have an error in your SQL syntax ... near ''ABC'' at line 1.

What Each Engine Answered

The example

SELECT 'abc' ILIKE 'ABC';

gave these answers:

Engine	Answer
SQLite 3.45.1	error: `near "'ABC'": syntax error`
PostgreSQL 16.15	`true`
MySQL 8.0.46	error: `You have an error in your SQL syntax ... near ''ABC'' at line 1`
MariaDB 10.11.14	error: `You have an error in your SQL syntax ... near ''ABC'' at line 1`

Every Form BareSQL Checks

BareSQL Portable checks 16 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form	SQLite	PostgreSQL	MySQL	MariaDB	What to write instead
`::`	no	yes	no	no	Use CAST(x AS type) (see CAST to an integer or a decimal for integers).
`ILIKE`	no	yes	no	no	Use LOWER(x) LIKE LOWER(pattern).
`DIV`	no	no	yes	yes	Use (a - a % b) / b for a whole-number result.
`MOD`	no	no	yes	yes	Use a % b.
`<=>`	no	no	yes	yes	Use (a = b OR (a IS NULL AND b IS NULL)).
`REGEXP`	yes	no	yes	yes	No form works on all four engines; ~ on PostgreSQL; REGEXP works in SQLite’s shell, not in every SQLite build.
`RLIKE`	no	no	yes	yes	No form works on all four engines; ~ on PostgreSQL.
`~`	no	yes	no	no	No form works on all four engines; REGEXP on MySQL and MariaDB.
`SIMILAR TO`	no	yes	no	no	Use LIKE where the pattern allows it.
`IS DISTINCT FROM`	yes	yes	no	no	Use (a <> b OR (a IS NULL) <> (b IS NULL)), or <=> on MySQL and MariaDB.
`XOR`	no	no	yes	yes	Use (a OR b) AND NOT (a AND b).
`&&`	no	no	yes	yes	Use AND.
`GLOB`	yes	no	no	no	Use LIKE, with % for * and _ for ?.
`==`	yes	no	no	no	Use =.
`!`	no	no	yes	yes	Use NOT.
`->>`	yes	no	no	no	No form works on all four engines; JSON paths and types differ on every engine.

What to Write Instead

This form answers the same on every engine:

SELECT LOWER('abc') LIKE LOWER('ABC');

Engine	Answer
SQLite 3.45.1	`1`
PostgreSQL 16.15	`true`
MySQL 8.0.46	`1`
MariaDB 10.11.14	`1`

Prefer the operators all four share: = <> < > <= >= + - * / %, LIKE, IN, BETWEEN, IS NULL. Leave || out: it joins text on SQLite and PostgreSQL and means OR on MySQL and MariaDB (see || joins text on some engines only).

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:14: error: The ILIKE operator works on PostgreSQL only; SQLite, MySQL and MariaDB refuse it. [operator-not-everywhere]
    ILIKE
    Use LOWER(x) LIKE LOWER(pattern).

1 problem: 1 error.

baresql rules operator-not-everywhere prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.