Create or Replace Temporary Type x as Enum not supported

[Alex-Monahan]

What happens?

Temporary types / temporary enums don't appear to be supported just yet.

I found a use case for this when creating an enum type to use as the columns to pivot outwards in a dynamic PIVOT (using the query table function). Since query can only execute a single statement, I need to create the enum ahead of time. Without a temporary type, I need to do a fair bit of bookkeeping to ensure transactional safety. The syntax I end up having to use is:

BEGIN;
DROP TYPE IF EXISTS columns_parameter_enum;
CREATE TYPE columns_parameter_enum AS ENUM (FROM build_my_enum('my_table_with_nulls', ['col2'] , []));

CREATE OR REPLACE TEMP TABLE my_results AS 
    FROM dynamic_aggregates('my_table_with_nulls', ['col1', 'col3', 'col4'], ['count(col1)'], ['col2'], [], subtotals:=1, grand_totals:=1);
DROP TYPE columns_parameter_enum;
COMMIT;

FROM my_results;

My ideal syntax would be:

CREATE OR REPLACE TEMPORARY TYPE columns_parameter_enum AS ENUM (FROM build_my_enum('my_table_with_nulls', ['col2'] , []));

FROM dynamic_aggregates('my_table_with_nulls', ['col1', 'col3', 'col4'], ['count(col1)'], ['col2'], [], subtotals:=1, grand_totals:=1);

(Note the dynamic_aggregates function uses the query table function together with a pivot and refers to the columns_parameter_enum)

To Reproduce

These do not work just yet:

CREATE TEMPORARY TYPE my_enum AS ENUM (SELECT 42 UNION ALL SELECT 43);

CREATE OR REPLACE TYPE my_enum AS ENUM (SELECT 42 UNION ALL SELECT 43);

CREATE OR REPLACE TEMPORARY TYPE my_enum AS ENUM (SELECT 42 UNION ALL SELECT 43);

All of the above return the same message:

Parser Error: syntax error at or near "TYPE"

This works:

DROP TYPE IF EXISTS my_enum;
CREATE TYPE my_enum AS ENUM (SELECT 42 UNION ALL SELECT 43);

OS:

MacOS

DuckDB Version:

v1.0.1-dev2074

DuckDB Client:

Python

Full Name:

Alex Monahan

Affiliation:

DuckDB Labs and MotherDuck

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a nightly build

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[hannes]

Thanks for filing, should be reasonably easy to add, but not a bug.