CODEWITHSUNDEEP
databasepostgresqlunique-constraint
b0xxed1n
b0xxed1n

Reputation: 2343

How to get constraints on indexes?

I'm creating something that needs to collect all types of data on your database, including the types of constraints used by each column. Unfortunately, it appears there is two ways to add constraints to a column (or maybe even more that I'm unaware of), you can either add a UNIQUE CONSTRAINT, or you can add an INDEX with UNIQUE on it.

I have this query:

select * from information_schema.table_constraints WHERE constraint_type='UNIQUE';

But the problem is that it does not detect UNIQUE constraints that are applied to an INDEX, it only detects UNIQUE CONSTRAINTS on their own, for example, it cannot find this:

Indexes: "index_videos_on_slug" UNIQUE, btree (slug)

Does anyone know of a way I can get this information? Thanks.

Upvotes: 0

Views: 476

Answers (1)

joop
joop

Reputation: 4513

  • the postgres catalogs live in the pg_catalog schema
  • information_schema is built on top of this (as a set of views)
  • information_schema is not complete, only the stuff that is dictated by the ANSI committee is implemented (more or less the GCD of all implementations)
  • indexes are not part of SQL (but most/all SQL implementations have them)

pg_index.indisunique (in the pg_catalog schema) is what you want.

BTW: if you want to promote an existing index to a constraint, you can use the alter ... using ... syntax:

ALTER TABLE ... ADD constraint ... USING your_index_name;

Upvotes: 1

Related Questions