7

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.

1
  • 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)
    – Dave Jones
    Apr 23, 2015 at 16:09

1 Answer 1

7

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;
3
  • Aaaaand here's a virtual pint of beer for you. Thank you so much, that was exactly what I was doing wrong.
    – s.m.
    Apr 22, 2015 at 14:24
  • Also, thanks for the regclass::oid trick that saves me a subselect.
    – s.m.
    Apr 22, 2015 at 14:27
  • 2
    WHERE attrelid = 'test_table'::regclass will do, since regclass already is an oid alias. A special one. Apr 22, 2015 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.