PostgreSQL hstore if contains value
Is there a way to check if a value in hstore exists in the request itself.
I have to store different values for each line (each line is an "item"). I need to check if an id already exists in the database in one of the hstore lines without having to select all over again and do loops, etc. Php. It seems that hstore is the only datatype that offers something like this, and also allows you to select the column for that row into an array.
Hstore may not be the best datatype for storing data like this, but there is nothing better.
The whole project is using 9.2 and I cannot change that - the json is in 9.3.
source to share
The function exist()
checks for a key. To determine if the key "42" exists anywhere in hstore.,.
select *
from (select test_id, exist(test_hs, '42') key_exists
from test) x
where key_exists = true;
test_id key_exists - 2 t
The function svals()
returns values as a set. You can query the result to determine if a specific value exists.
select *
from (select test_id, svals(test_hs) vals
from test) x
where vals = 'Wibble';
Hstore Operators and Functions
create table test (
test_id serial primary key,
test_hs hstore not null
);
insert into test (test_hs) values (hstore('a', 'b'));
insert into test (test_hs) values (hstore('42', 'Wibble'));
source to share