Postgres Commands

Postgres-Commands

1. Show all tables under a schema
SELECT table_name FROM information_schema.tables WHERE table_schema = '%SchemaName%';

2. Describe table information

SELECT 
    a.attname AS "Column Name",
    format_type(a.atttypid, a.atttypmod) AS "Data Type",
    CASE 
        WHEN a.attnotnull THEN 'YES'
        ELSE 'NO'
    END AS "Not Null",
    CASE 
        WHEN pk.attnum IS NOT NULL THEN 'YES'
        ELSE 'NO'
    END AS "Primary Key",
    CASE 
        WHEN fk.attnum IS NOT NULL THEN 'YES'
        ELSE 'NO'
    END AS "Foreign Key",
    CASE 
        WHEN uc.attnum IS NOT NULL THEN 'YES'
        ELSE 'NO'
    END AS "Unique"
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN (
    SELECT conrelid, unnest(conkey) AS attnum
    FROM pg_constraint
    WHERE contype = 'p'
) pk ON pk.conrelid = c.oid AND pk.attnum = a.attnum
LEFT JOIN (
    SELECT conrelid, unnest(conkey) AS attnum
    FROM pg_constraint
    WHERE contype = 'f'
) fk ON fk.conrelid = c.oid AND fk.attnum = a.attnum
LEFT JOIN (
    SELECT conrelid, unnest(conkey) AS attnum
    FROM pg_constraint
    WHERE contype = 'u'
) uc ON uc.conrelid = c.oid AND uc.attnum = a.attnum
WHERE c.relname = '%TableName%' 
  AND n.nspname = '%SchemaName%'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;