PostgreSQL User Defined Data Types not supported

[hilam]

What happens?

When I try attach a Postgres database where exists user defined data types, raise a duckdb.IOException: IO Error: Unsupported Postgres type <user_defined_type name>

To discover if a data type is user defined, do:

select 
	ud.typname as user_defined, 
	pd.typname as pg_defined, 
	ud.typtypmod as length_indicator,
	case 
		when (ud.typtypmod > 4 and pd.typname != 'numeric')
			then ud.typtypmod - 4 
		else 0 
	end as varchar_length
from 
	pg_catalog.pg_type ud                     -- user defined
	inner join pg_catalog.pg_type pd          -- postgres defined
		on pd."oid" = ud.typbasetype

Probably #57 is related.

Suggestion: Discover user defined data types, then change to postgres regular data types.

To Reproduce

Postgres:

CREATE DOMAIN my_type_v30 AS VARCHAR(30) NOT NULL;

CREATE DOMAIN my_id AS INT4;

CREATE TABLE my_table (
    table_id my_id PRIMARY KEY,
    table_var varchar(10),
    table_v30 my_type_v30
);

DuckDB/Python:

import duckdb

conn = duckdb.connect()  # create an in-memory database

try:
    conn.execute('LOAD postgres')
except duckdb.IOException:
    conn.execute('INSTALL postgres')
    conn.execute('LOAD postgres')

conn.execute("
    CALL POSTGRES_ATTACH('')
")

OS:

Linux

PostgreSQL Version:

13

DuckDB Version:

0.6.1

DuckDB Client:

Python

Full Name:

Hildeberto Magalhães

Affiliation:

NA

Have you tried this on the latest master 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

[hannes]

The sanest thing there is probably reading those things as strings