Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

PostgreSQL Compatibility

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

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
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.

## Functions

### `regexp_extract` Function

Unlike PostgreSQL's `regexp_substr` function, DuckDB's `regexp_extract` returns empty strings instead of `NULL`s when there is no match.

### `to_date` Function

DuckDB does not support the `to_date` PostgreSQL date formatting function. Instead, please use the `strptime` function.

### current_date / current_time / current_timestamp

DuckDB's `current_date` and `current_time` pseudo-columns return the current date (as `DATE`) and time (as `TIME`) in UTC, whereas PostgreSQL returns the current date (as `DATE`) in the configured local timezone and time as `TIMETZ`. For the current time in the configured timezone, still as regular `TIME`, DuckDB offers the function `current_localtime()`.

Both DuckDB and PostgreSQL return `current_timestamp` as `TIMESTAMPTZ`. DuckDB additionally offers `current_localtimestamp()`, which returns the time in the configured timezone as `TIMESTAMP`.

DuckDB does not currently offer `current_localdate()`; though this can be computed via `current_timestamp::DATE` or `current_localtimestamp()::DATE`.

> See the DuckDB blog entry on time zones for more information on timestamps and timezones and DuckDB's handling thereof.

## Resolution of Type Names in the Schema

For `CREATE TABLE` statements, DuckDB attempts to resolve type names in the schema where a table is created. For example:

    CREATE SCHEMA myschema;
    CREATE TYPE myschema.mytype AS ENUM ('as', 'df');
    CREATE TABLE myschema.mytable (v mytype);

PostgreSQL returns an error on the last statement:

    ERROR:  type "mytype" does not exist
    LINE 1: CREATE TABLE myschema.mytable (v mytype);

DuckDB runs the statement and creates the table successfully, confirmed by the following query:

    DESCRIBE myschema.mytable;

column_name | column_type | null | key | default | extra
--- | --- | --- | --- | --- | ---
v | ENUM('as', 'df') | YES | NULL | NULL | NULL

##### About this page

- Report content issue
- Edit this page on GitHub