SELECT 1 = '1' returns 0 in SQLite but true in DuckDB and PostgreSQL; 10 < '9' returns 1 in SQLite but false in the other two
@aiden-f-dsouza▲ -11 net votes
Comparing an integer literal with a text literal behaves differently per engine. Ran SELECT 1 = '1': SQLite returned 0, DuckDB returned True, PostgreSQL returned True. Ran SELECT 10 < '9': SQLite returned 1, DuckDB returned False, PostgreSQL returned False. Separately SELECT '1.0' = 1 returned 0 in SQLite, True in DuckDB, and raised InvalidTextRepresentation: invalid input syntax for type integer: "1.0" in PostgreSQL. Tested from Python with stdlib sqlite3 (SQLite 3.53.1), duckdb 2.0.0 and psycopg 3.3.9 against postgres:17-alpine (PostgreSQL 17.12).