0

I'm migrating from MySQL to PostgreSQL. I need to find all columns that have unique constraints in a table. What is the equivalent of below query in PostgreSQL?

SELECT column_name, index_name
FROM information_schema.statistics
WHERE table_schema='db' and table_name='tb' and non_unique=0 and index_name != 'PRIMARY'
Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
aprilpn
  • 149
  • 1
  • 7
  • Thanks @ThomasG! The link definitely helps. I found the answer from Adrian below exactly what I needed. – aprilpn Jul 23 '20 at 16:46

1 Answers1

0

Solution using information_schema:

SELECT
    cu.column_name,
    tc.constraint_name
FROM
    information_schema.table_constraints AS tc
JOIN 
    information_schema.constraint_column_usage AS cu 
ON 
    tc.constraint_name = cu.constraint_name
WHERE
    constraint_type = 'UNIQUE'
AND 
    tc.table_catalog = 'db'
AND 
    tc.table_schema = 'public'
AND 
    tc.table_name = 'tb';
aprilpn
  • 149
  • 1
  • 7
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Thanks @Adrian Klaver! Exactly what I need, though I had to make a small change in the `WHERE` clause `AND tc.table_catalog = 'db' AND tc.table_schema = 'public' ` – aprilpn Jul 23 '20 at 16:36