[Bug]: error when loading table metadata - pg_collation does not exist

[pavel-v-sobolev]

What happened?

I created a duckdb engine with this code:
engine = create_engine('duckdb:///:memory:', poolclass=StaticPool)
Then I call duckdb-engine like this to load a table definition:
table = Table(table_name, self.metadata, autoload_with=engine, schema=schema, extend_existing=True)
I get this error (looks like it tries to read postgres table):
_duckdb.CatalogException: Catalog Error: Table with name pg_collation does not exist!
Did you mean "pg_constraint"?

LINE 6: FROM pg_catalog.pg_collation

DuckDB Engine Version

0.17.0

DuckDB Version

1.4.4

SQLAlchemy Version

2.0.46

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct

[pcorpet]

I bet this is due to sqlalchemy/sqlalchemy#6511 added in sqlalchemy v2.0.45. It prevents to use more recent versions of sqlalchemy library with this engine.

[honglei]

@pcorpet #1393 works for me under Python3.13 x64/Win11/ duck 1.5.1/ SQLAlchemy 2.0.48 on https://github.com/eosphoros-ai/DB-GPT. @Mause Hope for a new version on Pypi with bug fixed, Thanks!

[pavel-v-sobolev]

@Mause, @pcorpet Hello! for me it is still crashing also on SQLAlchemy 2.0.48

[pcorpet]

@pavel-v-sobolev do you mean that you've tried my fix in #1393 and it's crashing?

What I understand:

• sqlalchemy 2.0.44 duckdb_engine 0.17.0 ✅
• sqlalchemy 2.0.45 duckdb_engine 0.17.0 ❌
• sqlalchemy 2.0.45 duckdb_engine 0.17.0 + fix: resolve pg_collation error in table reflection with SQLAlchemy 2.0.45+ #1393 ✅
• sqlalchemy 2.0.48 duckdb_engine 0.17.0 ❌ (what @pavel-v-sobolev tried)
• sqlalchemy 2.0.48 duckdb_engine 0.17.0 + fix: resolve pg_collation error in table reflection with SQLAlchemy 2.0.45+ #1393 ✅ (what @honglei tried)

[pavel-v-sobolev]

Hi @pcorpet ,

Both 2.0.48 and 2.0.49 are producing error that I described.
I have duckdb_engine 0.17.0, but if I look inside init.py, I don't see any _columns_query method override.
Looks like postgres dialect method is still in use.

Pavel

[honglei]

@pavel-v-sobolev TRY https://github.com/pcorpet/duckdb_engine/tree/fix-collation that include this https://github.com/Mause/duckdb_engine/pull/1393/changes , NOT the duckdb_engine 0.17.0.

[pavel-v-sobolev]

Hi @honglei !
I installed with:
uv pip install git+https://github.com/pcorpet/duckdb_engine@fix-collation

I get error, when i try to create engine:
engine = create_engine('duckdb:///data/data.ddb')
Error:

.venv/lib/python3.14/site-packages/duckdb_engine/init.py' has no attribute 'Dialect' (most likely due to a circular import)

[honglei]

https://github.com/pcorpet/duckdb_engine/blob/b36f9b80beebf3906a5fb977ee60fe1cb76160ee/duckdb_engine/__init__.py#L247
duckdb_engine/__init__.py file has Dialect,you should check what's wrong in your env. Python3.13/sqlalchemy.__version__='2.0.48',duckdb.__version__='1.5.1', do works.

import duckdb_engine
duckdb_engine.Dialect
from sqlalchemy.engine import create_engine
from sqlalchemy.sql import text
from sqlalchemy import MetaData, inspect
import sqlalchemy
import duckdb

print (f"{sqlalchemy.__version__=},{duckdb.__version__=}")
engine = create_engine("duckdb:///D:/PythonPrj/DB-GTP-test/olist_ecommerce.duckdb")

_metadata = MetaData()
_metadata.reflect(bind=engine)

[pavel-v-sobolev]

Hi @honglei , for me it is failing on line
import duckdb_engine

.venv/lib/python3.14/site-packages/duckdb_engine/init.py' has no attribute 'Dialect' (most likely due to a circular import)

[honglei]

@pavel-v-sobolev you need build a clean env， it works under Python3.14/sqlalchemy.__version__='2.0.49',duckdb.__version__='1.5.1'.

[pavel-v-sobolev]

Hi @honglei , you are right. it was my problem. I named my test file as duckdb.py and this resulted into conflict.
Now it is ok.