How to extract the names of unique columns of a table in PostgreSQL?

Question

Let's suppose I have a table in PorstreSQL defined as:

CREATE TABLE my_table (
  id serial not null primary key,
  var1 text null,
  var2 text null unique,
  var3 text null,
  var4 text null unique
);

Is there a query to information_schema that provides the names of unique columns only? The desirable response should be:

var2
var4

The query should ignore unique keys for multiple columns together.

Answer 1

You need information_schema.table_constraints and information_schema.constraint_column_usage:

SELECT table_schema, table_name, column_name
FROM information_schema.table_constraints AS c
   JOIN information_schema.constraint_column_usage AS cc
      USING (table_schema, table_name, constraint_name)
WHERE c.constraint_type = 'UNIQUE';

If you want to skip constraints with more than one column, use grouping:

SELECT table_schema, table_name, min(column_name)
FROM information_schema.table_constraints AS c
   JOIN information_schema.constraint_column_usage AS cc
      USING (table_schema, table_name, constraint_name)
WHERE c.constraint_type = 'UNIQUE'
GROUP BY table_schema, table_name
HAVING count(*) = 1;