SQL Spells

SELECT
   conname AS constraint_name,
   conrelid::regclass AS table_name,
   a.attname AS column_name,
   confrelid::regclass AS foreign_table_name,
   af.attname AS foreign_column_name
FROM
   pg_constraint AS c
JOIN
   pg_attribute AS a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
JOIN
   pg_attribute AS af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
WHERE
   c.contype = 'f' AND conrelid = 'your_table_name'::regclass;

SELECT
   kcu.column_name,
   tc.table_name,
   tc.constraint_name
FROM
   information_schema.table_constraints AS tc
JOIN
   information_schema.key_column_usage AS kcu
   ON tc.constraint_name = kcu.constraint_name
   AND tc.table_schema = kcu.table_schema
WHERE
   tc.constraint_type = 'PRIMARY KEY'
   AND tc.table_name = 'your_table_name'
   AND tc.table_schema = 'your_schema_name';  -- Typically 'public'

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table_name';