After `COPY DATABASE FROM <pg>`, the types from pg are improperly formatted in `EXPORT` commands

Start with the inline Node.js repro, especially the COPY FROM DATABASE and EXPORT DATABASE steps, then inspect repro/postcreate/schema.sql and the duckdb_types query output. Run the create and reopen commands to compare the generated SQL with the expected named or anonymous types. Done means EXPORT produces legal CREATE TYPE and CREATE TABLE statements that can be imported successfully.

What happens?

I have some custom enum and composite types in postgres. When I copy that entire database over to a duckdb instance, some things get garbled. First, let me show what I'm doind.

Repro using the @duckdb/node-api and @electric-sql/pglite-socket nodejs packages. mkdir repro && cd repro && pnpm init && pnpm add @duckdb/node-api==1.5.1-r.1 @electric-sql/pglite==0.4.3 @electric-sql/pglite-socket==0.1.3 and then node repro.js for the following js:

import { rm, mkdir } from 'node:fs/promises';
import duckdb from '@duckdb/node-api';
import { PGlite } from '@electric-sql/pglite';
import { PGLiteSocketServer } from '@electric-sql/pglite-socket';
import { join } from 'node:path';


async function create(repro_dir) {
    await rm(repro_dir, { recursive: true, force: true });
    await mkdir(repro_dir, { recursive: true });
    const path = join(repro_dir, 'dst.duckdb');

    const pgdb = await PGlite.create();
    pgdb.exec(`
            CREATE TYPE my_status_enum AS ENUM ('active', 'inactive');
            CREATE TYPE my_address AS (
                street TEXT,
                city TEXT,
                zip TEXT
            );
            CREATE TABLE t (id INTEGER, status my_status_enum, address my_address);
            INSERT INTO t VALUES (1, 'active', ROW('123 Main St', 'Anytown', '12345')), (2, 'inactive', ROW('456 Elm St', 'Othertown', '67890')), (3, NULL, NULL);
        `
    );
    const server = new PGLiteSocketServer({db: pgdb, host: 'localhost', port: 5432});
    await server.start();

    try {
        const dst = await duckdb.DuckDBInstance.create(path);
        const dstConn = await dst.connect();

        console.log('Attaching PGlite Postgres catalog and running COPY FROM DATABASE...');
        await dstConn.run('INSTALL postgres;');
        await dstConn.run('LOAD postgres;');
        await dstConn.run(
            'ATTACH \'host=localhost port=5432 dbname=postgres user=postgres sslmode=disable\' AS pg (TYPE postgres);'
        );

        const catalogReader = await dstConn.runAndReadAll('SELECT current_database();');
        const targetCatalog = String(catalogReader.getRows()[0][0]).replace(/"/g, '""');
        await dstConn.run(`COPY FROM DATABASE pg TO "${targetCatalog}";`);
        await dstConn.run(`EXPORT DATABASE '${join(repro_dir, 'postcreate')}' (FORMAT CSV);`);
        const results = await dstConn.runAndReadAll('SELECT * FROM duckdb_types WHERE type_name ILIKE \'my_%\';');
        console.log('Data in duckdb_types table after COPY FROM DATABASE:');
        console.table(results.getRows());
    } finally {
        await pgdb.close();
        await server.stop();
    }
};

async function reconnect(repro_dir){
    const path = join(repro_dir, 'dst.duckdb');
    console.log('Attempting to reopen destination database...');
    try {
        const instance = await duckdb.DuckDBInstance.create(path);
        const con = await instance.connect();
        await con.run(`EXPORT DATABASE '${join(repro_dir, 'after_reopen')}' (FORMAT CSV);`);
        const results = await con.runAndReadAll('SELECT * FROM duckdb_types WHERE type_name ILIKE \'my_%\';');
        console.log('Data in duckdb_types table after reopening database:');
        console.table(results.getRows());
        console.log('OK reopen succeeded');
    } catch (error) {
        console.log(`FAIL reopen failed: ${error instanceof Error ? error.message : String(error)}`);
    }
}

async function main() {
    const cmd = process.argv[2];
    const dir = process.argv[3] || 'repro';

    if (cmd === 'create') {
        await create(dir);
    } else if (cmd === 'reopen') {
        await reconnect(dir);
    } else {
        console.error(`Unknown command: ${cmd}`);
        process.exit(1);
    }
}

main();

After this runs, if I look at the repro/postcreate/schema.sql file, which was created from the EXPORT command, then I get this:

CREATE SCHEMA public;
CREATE TYPE public.my_address AS my_address
CREATE TYPE public.my_status_enum AS ENUM ( 'active', 'inactive' );
CREATE TABLE public.t(id INTEGER, status ENUM('active', 'inactive'), address STRUCT(street VARCHAR, city VARCHAR, zip VARCHAR));

Note how:

• CREATE TYPE public.my_address AS my_address isn't legal. If you actually tried to IMPORT from this directory you would get errors.
• CREATE TYPE public.my_status_enum AS ENUM ( 'active', 'inactive' ); is legal and worked.
• The CREATE TABLE statement decided to redefine the column types with anonymous types, instead of using the stored types.

What I would expect:

CREATE SCHEMA public;
CREATE TYPE public.my_address AS STRUCT(street VARCHAR, city VARCHAR, zip VARCHAR)
CREATE TYPE public.my_status_enum AS ENUM ( 'active', 'inactive' );
CREATE TABLE public.t(id INTEGER, status public.my_status_enum, address public.my_address);

Or, if that's not possible, then it would be less ideal, but workable if this just used the anonymous types and didn't generate illegal CREATE TYPE statements:

CREATE SCHEMA public;
CREATE TABLE public.t(id INTEGER, status ENUM('active', 'inactive'), address STRUCT(street VARCHAR, city VARCHAR, zip VARCHAR));

I have not tested on main for any of the packages.

To Reproduce

see above.

OS:

macOS

PostgreSQL Version:

17

DuckDB Version:

1.5.1

DuckDB Client:

nodejs

Full Name:

Nick Crews

Affiliation:

Ship Creek Group

Have you tried this on the latest main branch?

• I have NOT done this.

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