PostgreSQL tables schema

pg_tables_schema.sql

-- Table column attributes
SELECT
    c.table_name,
    c.column_name,
    c.ordinal_position,
    c.is_nullable,
    c.data_type,
    c.character_maximum_length,
    c.udt_name
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON
    c.table_name = t.table_name
    AND c.table_schema = t.table_schema
WHERE
    t.table_schema = 'public'
    AND t.table_type = 'BASE TABLE'
ORDER BY c.table_name, c.ordinal_position;

-- Table primary keys
SELECT
    kcu.table_name,
    kcu.column_name
FROM information_schema.key_column_usage kcu
INNER JOIN information_schema.columns c
ON
    c.table_name = kcu.table_name
    AND c.column_name = kcu.column_name
INNER JOIN information_schema.tables t
ON
    kcu.table_name = t.table_name
    AND kcu.table_schema = t.table_schema
INNER JOIN information_schema.table_constraints tc
ON
    tc.constraint_name = kcu.constraint_name
WHERE
    t.table_schema = 'public'
    AND t.table_type = 'BASE TABLE'
    AND tc.constraint_type = 'PRIMARY KEY'
ORDER BY kcu.table_name, c.ordinal_position;

-- Table foreign keys
SELECT
    kcu.table_name,
    kcu.column_name,
    kcufk.table_name,
    kcufk.column_name
FROM information_schema.key_column_usage kcu
INNER JOIN information_schema.columns c
ON
    c.table_name = kcu.table_name
    AND c.column_name = kcu.column_name
INNER JOIN information_schema.tables t
ON
    kcu.table_name = t.table_name
    AND kcu.table_schema = t.table_schema
INNER JOIN information_schema.table_constraints tc
ON
    tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON
    rc.constraint_name = tc.constraint_name
    AND tc.constraint_type = 'FOREIGN KEY'
LEFT JOIN information_schema.key_column_usage kcufk
ON
    kcufk.constraint_name = rc.unique_constraint_name
WHERE
    t.table_schema = 'public'
    AND t.table_type = 'BASE TABLE'
    AND tc.constraint_type = 'FOREIGN KEY'
ORDER BY kcu.table_name, c.ordinal_position;

-- Table unique keys
SELECT
    tt.table_name,
    tt.constraint_name,
    array_agg(tt.column_name::text) AS columns
FROM (
    SELECT
        kcu.constraint_name,
        kcu.table_name,
        kcu.column_name
    FROM information_schema.key_column_usage kcu
    INNER JOIN information_schema.tables t
    ON
        kcu.table_name = t.table_name
        AND kcu.table_schema = t.table_schema
    INNER JOIN information_schema.table_constraints tc
    ON
        tc.constraint_name = kcu.constraint_name
    WHERE
        t.table_schema = 'public'
        AND t.table_type = 'BASE TABLE'
        AND tc.constraint_type = 'UNIQUE'
    ORDER BY kcu.constraint_name, kcu.table_name, kcu.ordinal_position
) tt
GROUP BY tt.table_name, tt.constraint_name
ORDER BY tt.table_name, tt.constraint_name;