HSTORE Extension - Key-Value Store İçindir

Giriş
Açıklaması şöyle

hstore is a data type in PostgreSQL.It helps in implementing data in the form of key-value pairs for a single value. It is useful in scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are simply text strings.

Açıklaması şöyle.

Interestingly, the most "holistic" approach to polyglot persistence appears to be being taken by PostgreSQL. In their database, you can have a Key-Value store, JSON documents and of course, normal relational tables and one can perform SQL between and within these different storage types.

Örnek - key alana göre sorgulama

detail sütunu bir HSTORE sütunu olsun ve key alanları department, city, manager_name olsun. Şöyle yaparız

CREATE TABLE employee (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  detail HSTORE
);

INSER INTO employee (name,detail)
VALUES
 (
  'Divyanhs',
  '"department" => "Êngineering",
   "city" => "Delhi",
   "manager_name" => "Kuki"'
  ),
  ...
);

Bir key alana göre sorgulamak için şöyle yaparız

SELECT detail -> 'city' FROM employee;

Örnek

Şöyle yaparız. score sütünü HSTORE tipinden. İsim : sayı şeklinde bir JSON nesnesi yazarız

CREATE EXTENSION HSTORE;

CREATE TABLE hstore_example (score HSTORE);

INSERT INTO hstore_example VALUES('"Jason" => 100');
INSERT INTO hstore_example VALUES('"Jack" => 200');
INSERT INTO hstore_example VALUES('"Perry" => 150');

SELECT * FROM hstore_example WHERE score ? 'Jason'; //query rows

SELECT score -> 'Jason' AS score FROM hstore_example //query by key
WHERE score -> 'Jason' IS NOT NULL;