Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

2.0.0: Filter on a Postgres ENUM column fails with "Unsupported filter pushdown"

Open
  • Is something wrong?
#628 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
3/5
Estimated time
1-2 days
Newbie friendliness
68/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
cpp, postgresql
Domain
database

Research direction

Start with database-connector/src/table_scan/filter_pushdown.cpp and inspect how unsupported Postgres filters are handled in PostgresFilterPushdown::TransformFilters. Then read attach_filter_pushdown_or_enum.test for the existing enum pushdown coverage and add a regression case for a single enum comparison, including a combined filter. Done when those queries return the expected row without an unsupported pushdown error.

Written by the indexing model from the issue text.

Description

What happens?

Since DuckDB 2.0.0, filtering an attached Postgres table on a column of a Postgres ENUM type with a plain comparison against a string literal (enum_col = 'x') fails with:

Not implemented Error: Unsupported filter pushdown, use 'pg_enable_filter_pushdown=FALSE' to disable pushdowns. Problematic filter: "(m = 'happy')"

The same query works on 1.5.6. It also fails if the comparison is combined with other filters (id > 0 AND m = 'happy'), with an explicit cast (m::VARCHAR = 'happy', which the optimizer folds back into the enum comparison), and with a single-element IN (m IN ('happy')). A multi-element IN and the OR form covered by attach_filter_pushdown_or_enum.test still work.

In 1.5.6 the comparison was never pushed down: the plan shows a DuckDB FILTER on CAST(m AS VARCHAR) = 'happy' above the Postgres scan, while other filters such as id > 1 were pushed down. In 2.0.0 the plan shows Postgres Scan … Filters: m = 'happy', and execution then fails.

As far as I can tell, ENUM is not in IsTypeSupported in database-connector/src/table_scan/filter_pushdown.cpp, so the filter translates to an empty string, and PostgresFilterPushdown::TransformFilters then throws instead of leaving the filter to DuckDB. 1.5.x skipped untranslatable filters there.

Workaround: SET pg_enable_filter_pushdown = false. However, this also disables pushdown for every other filter, so it is a regression for queries that filtered on other columns. Another workaround is lower(m::VARCHAR) = 'happy', which the optimizer cannot fold, so the filter is evaluated in DuckDB as it was in 1.5.

Happy to send a PR. Would you rather support ENUM constants in the pushdown (writing them as quoted labels that Postgres casts), or keep enum comparisons out of pushdown as in 1.5?

To Reproduce

Start Postgres with an enum column:

docker run -d --rm --name pg-enum -e POSTGRES_PASSWORD=pw -p 55432:5432 postgres:18
until docker exec pg-enum pg_isready -h 127.0.0.1 -U postgres; do sleep 1; done
docker exec pg-enum psql -U postgres -c "
  create type mood as enum ('sad', 'happy');
  create table t (id int, m mood);
  insert into t values (1, 'sad'), (2, 'happy'), (3, null);"

Then in DuckDB 2.0.0:

INSTALL postgres;
LOAD postgres;
ATTACH 'host=localhost port=55432 dbname=postgres user=postgres password=pw' AS pg (TYPE postgres);

SELECT id FROM pg.t WHERE m = 'happy';
-- 1.5.6: 2
-- 2.0.0: Not implemented Error: Unsupported filter pushdown, use 'pg_enable_filter_pushdown=FALSE' to disable pushdowns. Problematic filter: "(m = 'happy')"

Side by side for both versions, as a self-contained uv script (uv run --with duckdb==1.5.6 repro.py vs uv run --with duckdb==2.0.0 repro.py):

# /// script
# requires-python = ">=3.12,<3.15"
# dependencies = ["duckdb"]
# ///
import duckdb

con = duckdb.connect()
con.execute("install postgres; load postgres")
con.execute("attach 'host=localhost port=55432 dbname=postgres user=postgres password=pw' as pg (type postgres)")
print("duckdb", duckdb.__version__)

for sql in [
    "select id from pg.t where m = 'happy'",
    "select id from pg.t where m::varchar = 'happy'",
    "select id from pg.t where m in ('happy')",
    "select id from pg.t where id > 0 and m = 'happy'",
    "select id from pg.t where m = 'happy' or m is null",
]:
    try:
        print("ok   ", sql, con.execute(sql).fetchall())
    except duckdb.Error as e:
        print("FAIL ", sql, "->", e)

A regression test in the style of attach_filter_pushdown_or_enum.test:

# name: test/sql/storage/attach_filter_pushdown_enum.test
# description: A comparison against a Postgres ENUM column is not pushed down, and does not fail
# group: [storage]

require postgres_scanner

require-env POSTGRES_TEST_DATABASE_AVAILABLE

statement ok
ATTACH 'dbname=postgresscanner' AS s (TYPE POSTGRES)

statement ok
DROP TABLE IF EXISTS s.filter_pushdown_enum

statement ok
DROP TYPE IF EXISTS s.filter_pushdown_enum_mood

statement ok
CREATE TYPE s.filter_pushdown_enum_mood AS ENUM ('sad', 'happy')

statement ok
CREATE TABLE s.filter_pushdown_enum(id INTEGER, m s.filter_pushdown_enum_mood)

statement ok
INSERT INTO s.filter_pushdown_enum VALUES (1, 'sad'), (2, 'happy'), (3, NULL)

query I
SELECT id FROM s.filter_pushdown_enum WHERE m = 'happy'
----
2

query I
SELECT id FROM s.filter_pushdown_enum WHERE id > 0 AND m = 'happy'
----
2
OS:

Linux x86_64 (Ubuntu)

PostgreSQL Version:

18.6 (postgres:18 Docker image)

DuckDB Version:

2.0.0, also 2.0.1.dev2610211350

DuckDB Client:

Python (also reproduced via JDBC 2.0.0.0)

Full Name:

Max Streese

Affiliation:

TTMzero GmbH

Have you tried this on the latest main branch?
  • I agree
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
  • I agree
Dominant language
C++
Stars
375
Forks
113
Avg merge
5h 29m
Merged PRs (30d)
37

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from duckdb/duckdb-postgres

All issues in duckdb/duckdb-postgres

Similar issues

More C++ issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.