hstore Key/Value store as a column type id title data 1 Object One model → "4563-a" 2 Smaller One model → "4563-a" scale → 0.43 3 Another One model → "874-b" scale → 2 engraving → "TEST"
hstore example CREATE EXTENSION hstore; CREATE TABLE widgets (id int, title text, data hstore); INSERT INTO widgets (id, text, data) VALUES (1, 'Object One', 'model => "8973-b", scale => 2'); SELECT * FROM widgets WHERE data->'scale' = '2'; id | title | data ----+------------+--------------------------------- 1 | Object One | "model"=>"8973-b", "scale"=>"2"
hstore example SELECT title, data->'model' AS model FROM widgets WHERE data ?| ARRAY['model', 'diagram']; title | model ------------+-------- Object One | 8973-b CREATE INDEX ON widgets USING gin (data); CREATE INDEX ON widgets ((data->'scale'));
json Structured JSON as a column type id title data 1 My Sofa {"colour": "brown"} 2 Your Sofa {"color"; "red", "weight": 2.6} 3 Their Sofa {"color": "blue", "weight": 0.8, "size": [4.5, 2.1, 1]}
json example CREATE TABLE sofas (id int, title text, data json); INSERT INTO sofas (id, title, data) VALUES (1, 'My Sofa', '{"colour": "brown", "size": [3, 4, 5]}'); SELECT title, data->>'colour' AS colour FROM sofas WHERE data->>'colour' = 'brown'; title | colour ---------+-------- My Sofa | brown
json example SELECT title, data->'size'->>0 AS length FROM sofas; title | length ---------+-------- My Sofa | 3 CREATE INDEX ON sofas ((data->>'colour'));
Example CREATE FUNCTION add_rating(id int, rating int) RETURNS float AS $$ var data = plv8.execute("SELECT count, total, average FROM ratings WHERE id = $1", [id])[0]; var newTotal = data.total + rating; var newCount = data.count + 1; var newAverage = (newTotal / newCount); plv8.execute("UPDATE ratings SET count = $1, total = $2, average = $3 WHERE id = $4", [newCount, newTotal, newAverage, id]); return newAverage; $$ LANGUAGE plv8; CREATE TABLE ratings (id int, count int, total int, average float);
Some things are too far... CREATE FUNCTION render_template(template text, context json) RETURNS text AS $$ import json from jinja2 import Template tinstance = Template(template) return tinstance.render(json.loads(context)) $$ LANGUAGE plpythonu; SELECT render_template('hello {{name}}', '{"name": "world"}'); render_template ----------------- hello world
Lock-free ALTER TABLE Only certain actions, including: ∙ Adding a column with NULL ∙ Dropping a column ∙ Converting very similar types (varchar → text)
andrewgodwin
yuhei_fujita
ivargrimstad
katsu8686
os1ma
fadis
okuramasafumi
morux2
clown0082
ostk0069
assu_ming
okuzawats
reverentgeek
aki_iinuma
lara
sstephenson
mthomps
wjessup
samlambert
vanstee
lynnandtonic
matthewcrist
morganepeng
csswizardry