Skip to content

Instantly share code, notes, and snippets.

@ivorpad

ivorpad/.sql

Created June 20, 2023 07:53
Show Gist options
  • Save ivorpad/043a99507382bc58d1870a83f6779ba8 to your computer and use it in GitHub Desktop.
Save ivorpad/043a99507382bc58d1870a83f6779ba8 to your computer and use it in GitHub Desktop.
Download ZIP
Database Introspection
Raw
.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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment