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
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'));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With