Anonymous - 2026-03-17

Originally posted by: Mazwak

Your query is (from mitm):
SELECT c.column_name, c.data_type, c.is_nullable, c.column_default, c.is_identity, c.character_maximum_length, (SELECT COUNT(*) FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = c.table_schema AND kcu.table_name = c.table_name AND kcu.column_name = c.column_name) > 0 as is_pk FROM information_schema.columns c WHERE c.table_schema = $1 AND c.table_name = $2 ORDER BY c.ordinal_position

But I have 0 rows in information_schema.table_constraints with the read-only user.
I don’t know why.

But I do have access to pg_constraint (and some other tables), so the following does work:

SELECT
    con.conname AS constraint_name,
    nsp.nspname AS source_schema,
    rel.relname AS source_table,
    att.attname AS source_column,
    fnsp.nspname AS target_schema,
    frel.relname AS target_table,
    fatt.attname AS target_column
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
JOIN unnest(con.conkey) WITH ORDINALITY AS cols(attnum, ord) ON TRUE
JOIN pg_attribute att ON att.attrelid = rel.oid AND att.attnum = cols.attnum
JOIN pg_class frel ON frel.oid = con.confrelid
JOIN pg_namespace fnsp ON fnsp.oid = frel.relnamespace
JOIN unnest(con.confkey) WITH ORDINALITY AS fcols(attnum, ord) ON fcols.ord = cols.ord
JOIN pg_attribute fatt ON fatt.attrelid = frel.oid AND fatt.attnum = fcols.attnum
WHERE con.contype = 'f'
ORDER BY source_schema, source_table;

I tried to see how dbeaver handles it, but failed.