COUNT DISTINCT With Two Columns Works on MySQL and Fails on PostgreSQL and SQLite

COUNT(DISTINCT customer_id, product_id) counts the distinct pairs on MySQL and MariaDB. SQLite and PostgreSQL take a single expression in COUNT(DISTINCT ...) and refuse it.

SQLite reports the wrong number of arguments and PostgreSQL a missing function.

SELECT COUNT(DISTINCT a, b) FROM t, run on four engines: SQLite 3.45.1 error: wrong number of arguments to function COUNT(); PostgreSQL 16.15 error: function count(integer, integer) does not exist; MySQL 8.0.46 2; MariaDB 10.11.14 2.

What Each Engine Answered

On a table made with

CREATE TABLE t (a INTEGER, b INTEGER);
INSERT INTO t VALUES (1, 1), (1, 1), (1, 2);

the example

SELECT COUNT(DISTINCT a, b) FROM t;

gave these answers:

Engine	Answer
SQLite 3.45.1	error: `wrong number of arguments to function COUNT()`
PostgreSQL 16.15	error: `function count(integer, integer) does not exist`
MySQL 8.0.46	`2`
MariaDB 10.11.14	`2`

What to Write Instead

This form answers the same on every engine:

SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t) AS s;

Engine	Answer
SQLite 3.45.1	`2`
PostgreSQL 16.15	`2`
MySQL 8.0.46	`2`
MariaDB 10.11.14	`2`

Count the rows of a DISTINCT subquery: SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t) AS s.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:8: error: COUNT(DISTINCT a, b) counts pairs on MySQL and MariaDB; SQLite and PostgreSQL take one expression only. [count-distinct-columns]
    COUNT(DISTINCT a, b)
    Count the rows of a DISTINCT subquery: SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t) AS s.

1 problem: 1 error.

baresql rules count-distinct-columns prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.