StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Another possibility is to use <a href="http://www.postgresql.org/docs/9.0/static/hstore.html" rel="nofollow">hstore</a> instead of EAV model.</p> <pre><code>CREATE TABLE character (id INTEGER, name VARCHAR, player_id INTEGER, attributes hstore); </code></pre> <p>That way you can store the attributes as a map (key - value).</p> <pre><code>insert into character (id, name, player_id, attributes) values (1, 'test', 1, '"attribute1"=>"value1","attribute2"=>"value2"') ,(2, 'test', 1, '"attribute1"=>"value1","attribute3"=>"value3"'); select (each(attributes)).key, (each(attributes)).value from character where id = 1; key text value text -------------------------- attribute1 value1 attribute2 value2 select id, attributes->'attribute3' as value from character WHERE exist(attributes,'attribute3'); id value --------------- 2 "value3" </code></pre> <p>Hope this helps.</p> <p><strong>UPDATE</strong></p> <p>I made a small benchmark to compare hstore vs two tables.</p> <pre><code>CREATE OR REPLACE FUNCTION create_dummy_data() RETURNS integer AS $BODY$ DECLARE cont1 INTEGER; cont2 INTEGER; sqlInsert VARCHAR; BEGIN CREATE TABLE character (id INTEGER PRIMARY KEY ,name VARCHAR ,player_id INTEGER); CREATE TABLE attributes (character_id INTEGER ,key VARCHAR ,value VARCHAR ,FOREIGN KEY (character_id) REFERENCES character); cont1 := 1; WHILE cont1 < 10000 LOOP sqlInsert := 'INSERT INTO character (id, name, player_id) VALUES (' || cont1 || ', ''character' || cont1 || ''', ' || cont1 || ');'; EXECUTE sqlInsert; cont1 := cont1 + 1; END LOOP; cont1 := 1; WHILE cont1 < 10000 LOOP cont2 := 1; WHILE cont2 < 10 LOOP sqlInsert := 'INSERT INTO attributes (character_id, key, value) VALUES (' || cont1 || ', ''key' || cont2 || ''', ' || cont2 || ');'; EXECUTE sqlInsert; cont2 := cont2 + 1; END LOOP; cont1 := cont1 + 1; END LOOP; CREATE TABLE character_hstore (id INTEGER ,name VARCHAR ,player_id INTEGER ,attributes hstore); cont1 := 1; WHILE cont1 < 10000 LOOP sqlInsert := 'INSERT INTO character_hstore (id, name, player_id, attributes) VALUES (' || cont1 || ', ''character' || cont1 || ''', ' || cont1 || ', ''"key1"=>"1","key2"=>"2","key3"=>"3","key4"=>"4","key5"=>"5"'');'; EXECUTE sqlInsert; cont1 := cont1 + 1; END LOOP; RETURN 1; END; $BODY$ LANGUAGE plpgsql; select * from create_dummy_data(); DROP FUNCTION create_dummy_data(); </code></pre> <p>And I've got the following results:</p> <pre><code>explain analyze SELECT ca.* FROM character ca JOIN attributes at ON ca.id = at.character_id WHERE at.value = '1'; "Hash Join (cost=288.98..2152.77 rows=10076 width=21) (actual time=2.788..23.186 rows=9999 loops=1)" CREATE INDEX ON attributes (value); explain analyze SELECT ca.* FROM character ca JOIN attributes at ON ca.id = at.character_id WHERE at.value = '1'; "Hash Join (cost=479.33..1344.18 rows=10076 width=21) (actual time=4.330..13.537 rows=9999 loops=1)" </code></pre> <p>And using hstore:</p> <pre><code>explain analyze SELECT * FROM character_hstore WHERE attributes @> 'key1=>1'; "Seq Scan on character_hstore (cost=0.00..278.99 rows=10 width=91) (actual time=0.012..3.530 rows=9999 loops=1)" explain analyze SELECT * FROM character_hstore WHERE attributes->'key1' = '1'; "Seq Scan on character_hstore (cost=0.00..303.99 rows=50 width=91) (actual time=0.016..4.806 rows=9999 loops=1)" </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload