unique index or constraint on hstore key

Question

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

Answer 1

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'));