In Postgres 9.4, I’m trying to pull the names of all columns that are involved in a
UNIQUE constraint for a given table in Postgres.
It looks like the names of such columns are contained in
pg_constraint. According to the docs, the column relevant to my problem is called
conkey, which happens to be an array of
The point is, the query I’ve come up with gives me the wrong results, and I’m pretty sure it’s because I’m joining on
conkey in the wrong way. The query is as follows:
SELECT pg_attribute.attname, pg_constraint.* FROM pg_attribute INNER JOIN pg_constraint ON pg_attribute.attnum = ANY (pg_constraint.conkey) WHERE pg_constraint.conrelid = ( SELECT oid FROM pg_class WHERE relname LIKE 'test_table' ) AND pg_constraint.contype = 'u'; -- to filter out non-unique constraints
Here’s a table DDL for a quick repro:
CREATE TABLE "test_table" ( "date" date DEFAULT now() NOT NULL, "foo" varchar COLLATE "default" NOT NULL CONSTRAINT "test_table_foo_key" UNIQUE ("foo") ) WITH (OIDS=FALSE); ALTER TABLE "test_table" OWNER TO "postgres";
Now, can anyone tell me what I’m doing wrong? Is there an easier way to get the information I want altogether?
I’m trying to retrieve the names of constrained table because the app I’m writing needs to work around the current lack of
UPSERT in Postgres. The idea is to do the insert-or-update with a CTE, but since I don’t know what the constrained columns are beforehand, I need to do this bit of introspection.
You half miss the connection between the two tables. The
...relid columns must match, too:
SELECT attname, c.* FROM pg_attribute a JOIN pg_constraint c ON attrelid = conrelid -- this was missing AND attnum = ANY (conkey) WHERE attrelid = 'test_table'::regclass;