Inner join on array column in Postgres

Question

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 ints.

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.

• Dave Jones: You might want to have a look at the auto_merge function in the suite I've been building; it sounds like it a does a lot of what you want (constructing an upsert-style CTE in SQL - although it explicitly doesn't deal with the repetition required by a true upsert solution).It's still a work in progress, but that bit is pretty much done (docs are still incomplete though)

Answer 1

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;

• s.m.: Aaaaand here's a virtual pint of beer for you. Thank you so much, that was exactly what I was doing wrong.
• s.m.: Also, thanks for the regclass::oid trick that saves me a subselect.
• Erwin Brandstetter: WHERE attrelid = 'test_table'::regclass will do, since regclass already is an oid alias. A special one.