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.

What do you mean by "ignore unique keys for multiple columns together"? — Laurenz Albe
– Laurenz Albe, Commented Dec 11, 2019 at 10:53
I mean the constraint for the unique combination of several fields. — Fomalhaut
– Fomalhaut, Commented Dec 11, 2019 at 11:22

Laurenz Albe · Accepted Answer · 2019-12-11 12:31:41Z

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;

edited Dec 11, 2019 at 12:31

answered Dec 11, 2019 at 10:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related