Stephan Hochdörfer // September 22 2017
@dburkes amazingly enough, document databases aren't
great at storing relational data. :p
— Sarah Mei (@sarahmei) 18. Januar 2011
"Schemaless" datatypes:
CREATE TABLE blogposts ( title text, content text, tags text[] );
CREATE TABLE tictactoe ( squares integer[3][3] );
INSERT INTO blogposts VALUES ( 'A blogpost', 'Lorem ipsum...', ARRAY['lorem', 'ipsum', 'blogpost'] ); INSERT INTO blogposts VALUES ( 'Another blogpost', 'Lorem ipsum...', ARRAY['lorem', 'ipsum'] );
SELECT tags[3] FROM blogposts;
tags ---------- blogpost (2 rows)
SELECT * from blogposts WHERE tags[2] = 'ipsum';
title | content | tags -----------------------+----------------+------------------------ A blogpost | Lorem ipsum... | {lorem,ipsum,blogpost} Another blogpost | Lorem ipsum... | {lorem,ipsum} (2 rows)
SELECT * FROM blogposts WHERE 'blogpost' = ANY(tags);
title | content | tags ---------------+----------------+------------------------ A blogpost | Lorem ipsum... | {lorem,ipsum,blogpost} (1 row)
SELECT * FROM blogposts WHERE tags && ARRAY['lorem', 'blogpost'];
title | content | tags -----------------------+----------------+------------------------ A blogpost | Lorem ipsum... | {lorem,ipsum,blogpost} Another blogpost | Lorem ipsum... | {lorem,ipsum} (2 rows)
SELECT * FROM blogposts WHERE tags @> ARRAY['lorem', 'blogpost']
title | content | tags ---------------+----------------+------------------------ A blogpost | Lorem ipsum... | {lorem,ipsum,blogpost} (1 row)
UPDATE blogposts SET tags = ARRAY['tag1', 'tag2', 'blogpost'] WHERE title = 'A blogpost';
SELECT * FROM blogposts;
title | content | tags -----------------------+----------------+---------------------- Another blogpost | Lorem ipsum... | {lorem,ipsum} A blogpost | Lorem ipsum... | {tag1,tag2,blogpost} (2 rows)
UPDATE blogposts SET tags[3] = 'tag3' WHERE title = 'A blogpost';
SELECT * FROM blogposts;
title | content | tags -----------------------+----------------+------------------ Another blogpost | Lorem ipsum... | {lorem,ipsum} A blogpost | Lorem ipsum... | {tag1,tag2,tag3} (2 rows)
UPDATE blogposts SET tags[5] = 'tag6' WHERE title = 'A blogpost';
SELECT * FROM blogposts;
title | content | tags -----------------------+----------------+---------------------------- Another blogpost | Lorem ipsum... | {lorem,ipsum} A blogpost | Lorem ipsum... | {tag1,tag2,tag3,NULL,tag6} (2 rows)
CREATE EXTENSION hstore;
CREATE TABLE products ( name text, price float, description text, metadata hstore );
INSERT INTO products VALUES ( 'Product A', 123.3, 'Lorem ipsum', 'color => black, weight => 3kg'::hstore ); INSERT INTO products VALUES ( 'Product B', 45.0, 'Lorem ipsum', 'color => red'::hstore );
SELECT metadata->'color' FROM products;
?column? ---------- black red (2 rows)
SELECT metadata->'weight' FROM products;
?column? ---------- 3kg (2 rows)
SELECT * FROM products WHERE metadata ? 'weight';
name | price | description | metadata -----------+-------+-------------+----------------------------------- Product A | 123.3 | Lorem ipsum | "color"=>"black", "weight"=>"3kg" (1 row)
SELECT * FROM products WHERE metadata @> 'color => red';
name | price | description | metadata -----------+-------+-------------+---------------- Product B | 45 | Lorem ipsum | "color"=>"red" (1 row)
UPDATE products SET metadata = 'color => black, weight => 5kg'::hstore WHERE name = 'Product A';
UPDATE products SET metadata = metadata || 'weight => 1kg'::hstore WHERE name = 'Product B';
UPDATE products SET metadata = metadata - 'weight'::text;
CREATE TABLE audit ( change_date timestamptz default now(), before hstore, after hstore );
create function audit() returns trigger language plpgsql as $$ begin INSERT INTO audit(before, after) SELECT hstore(old), hstore(new); return new; end; $$;
create trigger audit after update on example for each row execute procedure audit();
select change_date, after - before as diff from audit; change_date | diff -------------------------------+----------- 2013-11-08 09:29:19.808217+02 | "f1"=>"b" 2013-11-08 09:29:19.808217+02 | "f2"=>"c" (2 rows)
Source: Auditing Changes with Hstore
CREATE TABLE products ( name text, price float, description text, metadata json );
INSERT INTO products VALUES ( 'Product A', 123.3, 'Lorem ipsum', ' { "color": "black", "weight": "3kg" } ' ); INSERT INTO products VALUES ( 'Product B', 45.0, 'Lorem ipsum', ' { "color": "red" } ' );
INSERT INTO products VALUES ( 'Product C', 9.95, 'Lorem ipsum', ' { "color": ["red", "green", "blue"] } ' );
INSERT INTO products VALUES ( 'Product D', 9.95, 'Lorem ipsum', ' { "color": ["red", "green", "blue", ] } ' );
ERROR: invalid input syntax for type json LINE 5: ' { "color": ["red", "green", "blue", ] } ' ^ DETAIL: Expected JSON value, but found "]". CONTEXT: JSON data, line 1: { "color": ["red", "green", "blue", ]...
SELECT metadata->>'color' FROM products;
?column? -------------------------- black red ["red", "green", "blue"] (3 rows)
SELECT metadata#>'{color, 2}' FROM products;
?column? ---------- "blue" (3 rows)
UPDATE products SET metadata = ' { "color": ["red", "green", "blue", "yellow"] } ' WHERE name = 'Product C';
UPDATE products SET metadata = json_build_object('color', 'red') WHERE name = 'Product C';
UPDATE products SET metadata = json_set("metadata", '{"color"}', '"red"'::JSONB) WHERE name = 'Product C';
SELECT row_to_json(products) FROM products WHERE name = 'Product C';
row_to_json --------------------------------------------------------------------- {"name":"Product C","price":9.95,"description":"Lorem ipsum", "metadata": { "color": ["red", "green", "blue", "yellow"] } } (1 row)
SELECT row_to_json(t) FROM ( SELECT name, price FROM products WHERE name = 'Product C' ) t;
row_to_json ----------------------------------- {"name":"Product C","price":9.95} (1 row)
SELECT array_to_json(array_agg(row_to_json(t))) FROM ( SELECT name, price FROM products; ) t;
row_to_json ----------------------------------- [{"name":"Product A","price":123.3}, {"name":"Product B","price":45.0}, {"name":"Product C","price":9.95}] (1 row)
CREATE TABLE products ( name text, price float, description text, metadata jsonb );
INSERT INTO products VALUES ( 'Product A', 123.3, 'Lorem ipsum', ' { "color": "black", "weight": "3kg" } ' ); INSERT INTO products VALUES ( 'Product B', 45.0, 'Lorem ipsum', ' { "color": "red" } ' );
INSERT INTO products VALUES ( 'Product D', 9.95, 'Lorem ipsum', ' { "color": ["red", "green", "blue", ] } ' );
ERROR: invalid input syntax for type json LINE 5: ' { "color": ["red", "green", "blue", ] } ' ^ DETAIL: Expected JSON value, but found "]". CONTEXT: JSON data, line 1: { "color": ["red", "green", "blue", ]...
SELECT * FROM products WHERE metadata @> '{"color": "black"}';
name | price | description | metadata ------------+---------+-----------------+---------------------------- Product A | 123.3 | Lorem ipsum | {"color": "black", "weight": "3kg"} (1 row)
SELECT * FROM products WHERE metadata ? 'weight';
name | price | description | metadata ------------+---------+-----------------+---------------------------- Product A | 123.3 | Lorem ipsum | {"color": "black", "weight": "3kg"} (1 row)
SELECT * FROM products WHERE metadata ?| array['weight', 'color'];
name | price | description | metadata ------------+---------+-----------------+---------------------------- Product A | 123.3 | Lorem ipsum | {"color": "black", "weight": "3kg"} Product B | 45 | Lorem ipsum | {"color": "red"}
SELECT * FROM products WHERE metadata ?& array['weight', 'color'];
name | price | description | metadata ------------+---------+-----------------+---------------------------- Product A | 123.3 | Lorem ipsum | {"color": "black", "weight": "3kg"} (1 row)
CREATE INDEX metadata_fts ON products USING gin (( to_tsvector('english', metadata) ));
SELECT metadata->'color' FROM products WHERE to_tsvector('english', metadata) @@ to_tsquery('red | black');
CREATE TABLE imports ( importDate date, content XML );
INSERT INTO imports VALUES ( '2014-04-05', '<?xml version="1.0"?> <order customer="4711"> <product id="1234">10</product> <product id="9876">50</product> </order>'::xml ); INSERT INTO imports VALUES ( '2014-04-05', '<?xml version="1.0"?> <order customer="5432"> <product id="1234">3</product> </order>'::xml );
INSERT INTO imports VALUES ( '2014-04-05', '<?xml version="1.0"?> <open></close>'::xml );
ERROR: invalid XML content LINE 3: '<?xml version="1.0"?> ^ DETAIL: line 2: Opening and ending tag mismatch: open line 2 and close <open></close> ^ line 2: chunk is not well balanced <open></close> ^
SELECT xpath('/order/product/@id', content) FROM imports;
xpath ------------- {1234,9876} {1234} (2 rows)
SELECT UNNEST(xpath('/order/product/@id', content)::text[]) as productid, COUNT(*) as orders FROM imports GROUP BY productid;
productid | orders -----------+-------- 9876 | 1 1234 | 2 (2 rows)
SELECT * FROM imports WHERE xpath_exists('/order/product[@id=9876]', content);
importdate | content ------------+--------------------------------- 2014-04-05 | <order customer="4711"> + | <product id="1234">10</product>+ | <product id="9876">50</product>+ | </order> (1 row)
Thank you!
Do not forget to rate the talk:
https://joind.in/talk/acdb0