Comparing a Boolean With 1 Works on MySQL and SQLite and Fails on PostgreSQL

WHERE active = 1 is the usual way to test a flag on MySQL, and SQLite and MariaDB read it the same way. Moved to PostgreSQL, the query stops with an error.

On SQLite, MySQL and MariaDB a boolean is the number 0 or 1, and TRUE is another way to write 1. PostgreSQL has a real boolean type with no automatic conversion from integers, so code written for MySQL that compares flags with 1 stops there.

SELECT COUNT(*) FROM users WHERE active = 1, run on four engines: SQLite 3.45.1 1; PostgreSQL 16.15 error: operator does not exist: boolean = integer; MySQL 8.0.46 1; MariaDB 10.11.14 1.

What Each Engine Answered

On a table made with

CREATE TABLE users (active BOOLEAN);
INSERT INTO users VALUES (TRUE);

the example

SELECT COUNT(*) FROM users WHERE active = 1;

gave these answers:

Engine	Answer
SQLite 3.45.1	`1`
PostgreSQL 16.15	error: `operator does not exist: boolean = integer`
MySQL 8.0.46	`1`
MariaDB 10.11.14	`1`

Every Form BareSQL Checks

BareSQL Portable checks 3 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
`boolean = number`	yes	no	yes	yes
`WHERE number`	yes	no	yes	yes	Write a comparison: WHERE x <> 0, or WHERE 1 = 1.
`number into BOOLEAN`	yes	no	yes	yes	Insert TRUE or FALSE.

What to Write Instead

This form answers the same on every engine:

SELECT COUNT(*) FROM users WHERE active = TRUE;

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

Compare and insert TRUE and FALSE (active = TRUE, or simply WHERE active). All four engines accept them.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:34: error: PostgreSQL has no = between a boolean and a number. [boolean-as-number]
    active = 1
    Compare and insert TRUE and FALSE (active = TRUE, or simply WHERE active). All four engines accept them.

1 problem: 1 error.

baresql rules boolean-as-number prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.