LIMIT With a Comma Works on MySQL and SQLite and Fails on PostgreSQL

Paging code written for MySQL often says LIMIT 20, 10: skip 20 rows, return 10. SQLite and MariaDB read it the same way. PostgreSQL refuses it.

In LIMIT a, b the first number is the offset and the second the count, the reverse of the order in which LIMIT ... OFFSET reads.

What Each Engine Answered

On a table made with

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (1), (2), (3);

the example

SELECT x FROM t ORDER BY x LIMIT 1, 2;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT x FROM t ORDER BY x LIMIT 2 OFFSET 1;

Write LIMIT count OFFSET offset, which all four accept: LIMIT 1, 2 becomes LIMIT 2 OFFSET 1.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:26: info: SQLite, MySQL and MariaDB put NULL first in ascending order; PostgreSQL puts it last. [null-order]
    x
    Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.
example.sql:3:28: error: LIMIT offset, count works on SQLite, MySQL and MariaDB; PostgreSQL refuses it. [limit-comma]
    LIMIT 1, 2
    Write LIMIT count OFFSET offset, which all four accept: LIMIT 1, 2 becomes LIMIT 2 OFFSET 1.

2 problems: 1 error, 1 note.

The example also runs into Where NULL sorts, which has a post of its own.

baresql rules limit-comma prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.