I need to list all column names of a table and show whether or not it has a unique index applied to it. I can show all unique indexes per table/schema but not per column. This is the closes I have come to it:
select
ns.nspname as schema,
idx.indrelid :: REGCLASS as table_name,
idx.indisunique as indu,
i.relname from pg_index as idx
join pg_class as i on i.oid = idx.indexrelid
join pg_namespace as ns on i.relnamespace = ns.oid
where NOT ns.nspname like 'pg%';
I don't own a PostgreSQL 11 DB for testing, so I am not quite sure if there is no error, but have a look at this (you can choose to omit the WHERE, but I guess system columns are not relevant to you, perhaps excluding oid):
SELECT
n.nspname,
c.relname,
a.attname,
(i.indisunique IS TRUE) AS part_of_unique_index
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
LEFT JOIN pg_index i
ON i.indrelid = c.oid
AND a.attnum = ANY (i.indkey[0:(i.indnkeyatts - 1)])
WHERE a.attnum > 0;
NOTE: INCLUDE is new for indexes in PostgreSQL 11. If you are on a lower version, use the following (you can also use this, if you also want to see the non-key-columns in PostgreSQL 11):
SELECT
n.nspname,
c.relname,
a.attname,
(i.indisunique IS TRUE) AS part_of_unique_index
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
LEFT JOIN pg_index i
ON i.indrelid = c.oid AND a.attnum = ANY (i.indkey)
WHERE a.attnum > 0;
NOTE2: This answer has been edited, saying indnkeyatts is the wrong field and you should use indnatts instead. I disagree with this, as this means that columns, which are only in the INCLUDE part of a unique index, would also be marked as having a unique constraint applied to them. But in fact, they are only stored in the index, but they do NOT need to be unique. So I think for the original poster, indnkeyatts is the way to go. If you want to see only included columns as well, use indnatts.
This also seems to work, perhaps, a bit simpler.
SELECT c.table_name, c.column_name, t.*
FROM information_schema.table_constraints AS t
INNER JOIN information_schema.constraint_column_usage AS c
ON t.constraint_name = c.constraint_name
AND c.constraint_schema = t.table_schema
WHERE t.constraint_type IN ('UNIQUE', 'PRIMARY KEY', 'FOREIGN KEY' )
AND t.table_schema = 'users'
AND t.table_name = 'users';
