8

I would like to create a unique index or constraint on a specific keys in an hstore column if that key exists. I was hoping the answer would be found somewhere in this other question:

Practical limitations of expression indexes in PostgreSQL

But I tried every version of the syntax I could come up with and nothing would work.

currently, my table is

hstore_table

the hstore field is hstore_value

and they keys I would like to force to be unique are 'foo' and 'bar' when they exist.

My version of PostgreSQL is 8.4.13

Community
  • 1
  • 1
trex005
  • 5,015
  • 4
  • 28
  • 41

1 Answers1

17

If I've understood what you're asking for correctly, you want a partial unique functional index:

CREATE TABLE hstest ( x hstore not null );

CREATE UNIQUE INDEX hstest_key_k1_values_unique 
ON hstest((x -> 'k1'))
WHERE ( x ? 'k1' );

The WHERE clause isn't strictly required as the key lookup will be null if it's not found. Whether it's appropriate will depend on your queries.

If you want multiple keys, use two indexes if you want the two to be independent, or index two expressions if you want to link them so the unique constraint allows (1,2) and (1,3) or (2,2) but not another (1,2), like this:

CREATE UNIQUE INDEX hstest_key_k1k2_values_unique 
ON hstest ((x -> 'k1'), (x -> 'k2'));
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • The documentation states "Each key in an hstore is unique." I don't get why we'd need to add the type of index you show. Am I missing something? – IamIC Dec 28 '16 at 19:34
  • 2
    @IamIC The original poster wished to ensure that for two different rows A and B, the hstore field 'h' could not contain the same key 'k' with the same value between the two rows. i.e. if there exists row `A.h{k=1}` disallow insertion of row `B.h{k=1}` but allow insertion of `B.h{k=2}` or `B.h{x=1}`. Like a `UNIQUE` constraint, but applying on the hstore key's value, not the column as a whole. – Craig Ringer Dec 30 '16 at 04:41
  • @CraigRinger Thank you. That makes sense. Looks like one would have to specify each key to be constrained in the index definition. Wouldn't be practical for a large number of keys. I guess a before upsert trigger would work in such cases. – IamIC Dec 31 '16 at 08:56
  • 1
    @IamIC Because hstore has an `=` operator you could probably do it with the hstore subset operator `hstore -> text[]` but only if you're interested in a fixed key-list and are OK with the null handling semantics there. A before-insert trigger would NOT work due to MVCC visibility rules; you cannot correctly implement a `unique` constraint with a trigger. – Craig Ringer Jan 01 '17 at 06:17