Software Engineering :: Database :: PostgreSQL :: HStore :: Examples

readme.md

Software Engineering :: Database :: PostgreSQL :: HStore :: Examples

⪼ Made with 💜 by realpolyglot.com

• Software Engineering :: Database :: PostgreSQL :: HStore

enable the extension

CREATE EXTENSION HSTORE;

create a key-value store

CREATE TABLE o365_user_device (map hstore);

create an index for the key-value store

CREATE INDEX o365_user_device_gin_idx ON o365_user_device USING GIN (map);	

insert values into key-value store

INSERT INTO o365_user_device values('"2a610f6f-adf6-4205" => "f9a8d62c-1b47-4e2a-b573-eb8142c6e5d8"');
INSERT INTO o365_user_device values('"3b7c24e8-3f91-1a2b" => "416287bc-04f6-4e1a-b7dc-4d1a8217b2f5"');
INSERT INTO o365_user_device values('"4e5d90b2-6d23-78c4" => "f23ad25e-5c2d-4d31-a003-4ef9dc83e372"');
INSERT INTO o365_user_device values('"a2f347c1-8b59-0e6d" => "98da3e77-0e98-4f9b-8324-91f89bafcc61"');
INSERT INTO o365_user_device values('"5f1d3b2a-c4e7-9f0b" => "7ca1bf16-28fe-4212-8be7-642687a91a8f"');
INSERT INTO o365_user_device values('"6a8e23c5-7d10-2c3d" => "b6df5a9d-6947-42e5-962b-e27e8ea8a057"');
INSERT INTO o365_user_device values('"b9c4f0e2-5a6b-8419" => "eca4d5e3-4125-4be3-9db1-2e0b876fc782"');
INSERT INTO o365_user_device values('"0d2a57e3-3f98-7e1c" => "0f358fbd-6c2d-4f5a-b710-e3c08c1e881a"');
INSERT INTO o365_user_device values('"8c3b942f-d217-6a5b" => "f6d4219a-a22d-4790-8133-8f67a37a5c32"');
INSERT INTO o365_user_device values('"e4f21d8a-b3c2-0914" => "2a6e21f0-19a8-4a44-94a0-7d03c58e5413"');
INSERT INTO o365_user_device values('"7d9e02b5-8f4a-5d7e" => "a0e4d3c2-273d-4a2b-af6e-125748f7c0b7"');

SELECT by Key display ALL Columns

SELECT * FROM o365_user_device WHERE map ? '2a610f6f-adf6-4205';	

SELECT by Key display Value Column and rename Value as device_id

SELECT map -> '2a610f6f-adf6-4205' as device_id FROM o365_user_device WHERE map ? '2a610f6f-adf6-4205';