PostgreSQL - List all UNIQUE constraints in a schema-CodePudding

I'd like to figure a better way to list all UNIQUE constraints in a specific schema. I was able to list them using this below query from old answers to similar questions:

SELECT
    tc.table_schema,
    tc.constraint_name,
    tc.table_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_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
JOIN
    information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE
    tc.constraint_type = 'UNIQUE' AND tc.table_schema = 'mySchema'

But this query don't return pretty results. For example if I have this table here:

CREATE TABLE myTable (
    id int not null primary key,
    col1 text,
    col2 text,
    col3 text,
    unique (col1, col2, col3)
)

The SELECT statement will return all 9 rows as a result for each column with the other columns contributed in the UNIQUE constraint -including itself-.

Sample output

"public"    "mytable_col1_col2_col3_key"    "mytable"   "col1"  "public"    "mytable"   "col1"
"public"    "mytable_col1_col2_col3_key"    "mytable"   "col1"  "public"    "mytable"   "col2"
"public"    "mytable_col1_col2_col3_key"    "mytable"   "col1"  "public"    "mytable"   "col3"
"public"    "mytable_col1_col2_col3_key"    "mytable"   "col2"  "public"    "mytable"   "col1"
"public"    "mytable_col1_col2_col3_key"    "mytable"   "col2"  "public"    "mytable"   "col2"
"public"    "mytable_col1_col2_col3_key"    "mytable"   "col2"  "public"    "mytable"   "col3"
"public"    "mytable_col1_col2_col3_key"    "mytable"   "col3"  "public"    "mytable"   "col1"
"public"    "mytable_col1_col2_col3_key"    "mytable"   "col3"  "public"    "mytable"   "col2"
"public"    "mytable_col1_col2_col3_key"    "mytable"   "col3"  "public"    "mytable"   "col3"

Expected output

"public"    "mytable_col1_col2_col3_key"    "mytable"   ["col1","col2","col3"]

So can anyone help me fix this query to return a prettified representation of the UNIQUE constraint?

CodePudding user response：

You can query the metadata directly:

SELECT c.conrelid::regclass AS table_name,
       c.conname AS constraint_name,
       array_agg(a.attname ORDER BY k.n) AS columns
FROM pg_constraint AS c
   CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS k(c,n)
   JOIN pg_attribute AS a
      ON a.attnum = k.c AND a.attrelid = c.conrelid
WHERE c.contype = 'u'
  AND c.connamespace = 'mySchema'::regnamespace
GROUP BY c.oid;