6

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.

  • 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 '15 at 16:09
6

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;
  • 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 '15 at 14:24
  • Also, thanks for the regclass::oid trick that saves me a subselect. – s.m. Apr 22 '15 at 14:27
  • 2
    WHERE attrelid = 'test_table'::regclass will do, since regclass already is an oid alias. A special one. – Erwin Brandstetter Apr 22 '15 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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