Исправление theNamelessOne, (текущая версия) :
Тогда Psql + hstore — то, что доктор прописал:
postgres=# create table adhoc_kv (
postgres(# key hstore not null,
postgres(# value text not null
postgres(# );
CREATE TABLE
postgres=# create unique index on adhoc_kv(key);
CREATE INDEX
postgres=# insert into adhoc_kv(key, value) VALUES ('a => b, c => d'::hstore, 'value 1'), ('a => b, x => y'::hstore, 'value 2');
INSERT 0 2
postgres=# -- look, ma, unique index!
postgres=# insert into adhoc_kv(key, value) VALUES ('c => d, a => b'::hstore, 'ignored value') on conflict(key) do nothing;
INSERT 0 0
postgres=# select * from adhoc_kv;
key | value
--------------------+---------
"a"=>"b", "c"=>"d" | value 1
"a"=>"b", "x"=>"y" | value 2
(2 rows)
postgres=# -- exact match
postgres=# select value from adhoc_kv where key = 'c => d, a => b'::hstore;
value
---------
value 1
(1 row)
postgres=# -- "wildcard" search
postgres=# select value from adhoc_kv where key @> 'a => b'::hstore;
value
---------
value 1
value 2
(2 rows)Исходная версия theNamelessOne, :
Тогда Psql + hstore — то, что доктор прописал:
postgres=# create table adhoc_kv (
postgres(# key hstore not null,
postgres(# value text not null
postgres(# );
CREATE TABLE
postgres=# create unique index on adhoc_kv(key);
CREATE INDEX
postgres=# insert into adhoc_kv(key, value) VALUES ('a => b, c => d'::hstore, 'value 1'), ('a => b, x => y'::hstore, 'value 2');
INSERT 0 2
postgres=# -- look, ma, unique index!
postgres=# insert into adhoc_kv(key, value) VALUES ('c => d, a => b'::hstore, 'ignored value') on conflict(key) do nothing;
INSERT 0 0
postgres=# select * from adhoc_kv;
key | value
--------------------+---------
"a"=>"b", "c"=>"d" | value 1
"a"=>"b", "x"=>"y" | value 2
(2 rows)
postgres=# -- exact match
postgres=# select value from adhoc_kv where key = 'c => d, a => b'::hstore;
value
---------
value 1
(1 row)
postgres=# -- "wildcard" search
postgres=# select value from adhoc_kv where key @> 'a => b'::hstore;
value
---------
value 1
value 2
(2 rows)
postgres=#