Database Introspection

.sql

-- Generates multiple CREATE TABLE statements (without INDEXES)

SELECT string_agg(create_table_stmt, E'\n\n' ORDER BY tablename) 
FROM (
    SELECT 
        tablename, 
        'CREATE TABLE ' || tablename || ' (' || column_defs || ');' as create_table_stmt
    FROM (
        SELECT 
            c.relname AS tablename,
            string_agg(a.attname || ' ' || pg_catalog.format_type(a.atttypid, a.atttypmod), ', ' ORDER BY a.attnum) AS column_defs
        FROM pg_catalog.pg_attribute a
        LEFT JOIN pg_catalog.pg_class c ON a.attnum > 0 AND a.attrelid = c.oid
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'::char AND n.nspname = 'public' AND NOT a.attisdropped
        GROUP BY c.relname
    ) sub1
) sub2;