current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
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 at 16:09

1 Answer 1

up vote 3 down vote accepted

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::oid;
share|improve this answer
    
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 at 14:24
    
Also, thanks for the regclass::oid trick that saves me a subselect. –  s.m. Apr 22 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 at 19:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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