1

I'm on DuckDB 1.4.1 experiencing difficulty updating a Postgres 17.6 ENUM field status:

CREATE TYPE mystatus_enum AS ENUM (
    'IN_STOCK', 'OUT_OF_STOCK', 'NOT_FOUND', 'NOT_A_PRODUCT'
);

CREATE TABLE mytable
(
    id INTEGER primary key,
    status mystatus_enum
);

All of the following give me:

Not implemented Error: Enums in Postgres must be named - unnamed enums are not supported. Use CREATE TYPE to create a named enum.

Making a similar update from within Postgres without DuckDB is not a problem.

Both for DuckDB sources:

update mypg.mytable set status=ddb.status where ddb.id=mypg.mytable.id;
update mypg.mytable set status=ddb.status::varchar where ddb.id=mypg.mytable.id;
update mypg.mytable set status=ddb.status::text where ddb.id=mypg.mytable.id;
update mypg.mytable set status=ddb.status::mypg.mystatus_enum where ddb.id=mypg.mytable.id;

and PG sources:

update mypg.mytable set status=mypg.mytable2.status where mypg.mytable2.id=mypg.mytable.id;

and as above but with PG sources.

I've also tried using an equivalent ENUM declared in DuckDB.

Any suggestions for how I can avoid this error? I'd like to avoid my last resort of switching the Postgres field from ENUM to VARCHAR as it is a very large and active table.

5
  • To your question text add the table definition for mypg.mytable. Commented Oct 30 at 21:08
  • I have now done so Commented Oct 30 at 21:27
  • Yeah, I can replicate. Not sure why CREATE TYPE mystatus_enum AS ENUM ( 'IN_STOCK', 'OUT_OF_STOCK', 'NOT_FOUND', 'NOT_A_PRODUCT'); is not being seen as named. My guess is something in the extension code is not picking up the correct information from the system catalogs. I would suggest filing an issue here DuckDB-Postgres Issues Commented Oct 30 at 21:48
  • 2
    thanks, have done so github.com/duckdb/duckdb-postgres/issues/379 Commented Oct 30 at 22:02
  • 1
    Use lookup tables! Commented Oct 31 at 2:08

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.