DuckDB's SQL dialect closely follows the conventions of the PostgreSQL dialect. The few exceptions to this are listed on this page.
Floating-Point Arithmetic
DuckDB and PostgreSQL handle floating-point arithmetic differently for division by zero. DuckDB conforms to the IEEE Standard for Floating-Point Arithmetic (IEEE 754) for both division by zero and operations involving infinity values. PostgreSQL returns an error for division by zero but aligns with IEEE 754 for handling infinity values. To show the differences, run the following SQL queries:
SELECT 1.0 / 0.0 AS x;
SELECT 0.0 / 0.0 AS x;
SELECT -1.0 / 0.0 AS x;
SELECT 'Infinity'::FLOAT / 'Infinity'::FLOAT AS x;
SELECT 1.0 / 'Infinity'::FLOAT AS x;
SELECT 'Infinity'::FLOAT - 'Infinity'::FLOAT AS x;
SELECT 'Infinity'::FLOAT - 1.0 AS x;
Expression | PostgreSQL | DuckDB | IEEE 754 |
---|---|---|---|
1.0 / 0.0 | error | Infinity | Infinity |
0.0 / 0.0 | error | NaN | NaN |
-1.0 / 0.0 | error | -Infinity | -Infinity |
'Infinity' / 'Infinity' | NaN | NaN | NaN |
1.0 / 'Infinity' | 0.0 | 0.0 | 0.0 |
'Infinity' - 'Infinity' | NaN | NaN | NaN |
'Infinity' - 1.0 | Infinity | Infinity | Infinity |
Division on Integers
When computing division on integers, PostgreSQL performs integer division, while DuckDB performs float division:
SELECT 1 / 2 AS x;
PostgreSQL returns:
x
---
0
(1 row)
DuckDB returns:
x |
---|
0.5 |
To perform integer division in DuckDB, use the //
operator:
SELECT 1 // 2 AS x;
x |
---|
0 |
UNION
of Boolean and Integer Values
The following query fails in PostgreSQL but successfully completes in DuckDB:
SELECT true AS x
UNION
SELECT 2;
PostgreSQL returns an error:
ERROR: UNION types boolean and integer cannot be matched
DuckDB performs an enforced cast, therefore, it completes the query and returns the following:
x |
---|
1 |
2 |
Case Sensitivity for Quoted Identifiers
PostgreSQL is case-insensitive. The way PostgreSQL achieves case insensitivity is by lowercasing unquoted identifiers within SQL, whereas quoting preserves case, e.g., the following command creates a table named mytable
but tries to query for MyTaBLe
because quotes preserve the case.
CREATE TABLE MyTaBLe(x INT);
SELECT * FROM "MyTaBLe";
ERROR: relation "MyTaBLe" does not exist
PostgreSQL does not only treat quoted identifiers as case-sensitive, PostgreSQL treats all identifiers as case-sensitive, e.g., this also does not work:
CREATE TABLE "PreservedCase"(x INT);
SELECT * FROM PreservedCase;
ERROR: relation "preservedcase" does not exist
Therefore, case-insensitivity in PostgreSQL only works if you never use quoted identifiers with different cases.
For DuckDB, this behavior was problematic when interfacing with other tools (e.g., Parquet, Pandas) that are case-sensitive by default - since all identifiers would be lowercased all the time. Therefore, DuckDB achieves case insensitivity by making identifiers fully case insensitive throughout the system but preserving their case.
In DuckDB, the scripts above complete successfully:
CREATE TABLE MyTaBLe(x INT);
SELECT * FROM "MyTaBLe";
CREATE TABLE "PreservedCase"(x INT);
SELECT * FROM PreservedCase;
SELECT table_name FROM duckdb_tables();
table_name |
---|
MyTaBLe |
PreservedCase |
PostgreSQL's behavior of lowercasing identifiers is accessible using the preserve_identifier_case
option:
SET preserve_identifier_case = false;
CREATE TABLE MyTaBLe(x INT);
SELECT table_name FROM duckdb_tables();
table_name |
---|
mytable |
However, the case insensitive matching in the system for identifiers cannot be turned off.
Using Double Equality Sign for Comparison
DuckDB supports both =
and ==
for quality comparison, while Postgres only supports =
.
SELECT 1 == 1 AS t;
DuckDB returns:
┌─────────┐
│ t │
│ boolean │
├─────────┤
│ true │
└─────────┘
Postgres returns:
postgres=# SELECT 1 == 1 AS t;
ERROR: operator does not exist: integer == integer
LINE 1: SELECT 1 == 1 AS t;
Note that the use of ==
is not encouraged due to its limited portability.
Vacuuming tables
In PostgreSQL, the VACUUM
statement garbage collects tables and analyzes tables.
In DuckDB, the VACUUM
statement is only used to rebuild statistics.
For instruction on reclaiming space, refer to the “Reclaiming space” page.