arrays

CREATE TABLE permissions (
   id serial PRIMARY KEY,   user_id integer,   perm varchar[]);

INSERT INTO permissions (user_id, perm) VALUES (
   1,
   '{"read","write","admin"}'
);

INSERT INTO permissions (user_id, perm) VALUES (
   2,   '{"read"}'
);

INSERT INTO permissions (user_id, perm) VALUES (
   3,   '{"read","write"}'
);

SELECT * FROM permissions;

 id | user_id |        perm
----+---------+--------------------
  1 |       1 | {read,write,admin}
  2 |       2 | {read}
  3 |       3 | {read,write}

SELECT user_id, perm FROM permissions WHERE 'write' = ANY(perm);

 user_id |        perm
---------+--------------------
       1 | {read,write,admin}
       3 | {read,write}

SELECT user_id, unnest(perm) FROM permissions WHERE 'write' = ANY(perm);

 user_id | unnest
---------+--------
       1 | read
       1 | write
       1 | admin
       3 | read
       3 | write

Resources

hstore

CREATE EXTENSION hstore;
CREATE TABLE items (
   id serial PRIMARY KEY,   name varchar,   price real,   attributes hstore);

INSERT INTO items (name, price, attributes) VALUES (
   'MacBook Air',   999.99,   'osx_version => "10.8",
    ram => "8G"'
);

INSERT INTO items (name, price, attributes) VALUES (
   'Google Galaxy Nexus',
   99.99,
   'android_version => "4.3",
    ram => "2G"'
);

INSERT INTO items (name, price) VALUES (
   'Kleenex',
   4.99
);

SELECT * FROM items;

 id |        name         | price  |              attributes
----+---------------------+--------+---------------------------------------
  1 | MacBook Air         | 999.99 | "ram"=>"8G", "osx_version"=>"10.8"
  2 | Google Galaxy Nexus |  99.99 | "ram"=>"2G", "android_version"=>"4.3"
  3 | Kleenex             |   4.99 |

SELECT name, price, attributes->'osx_version' as osx FROM items WHERE attributes->'osx_version' = '10.8';

    name     | price  | osx
-------------+--------+------
 MacBook Air | 999.99 | 10.8

SELECT name, price, attributes->'ram' as ram FROM items WHERE attributes ? 'ram';

        name         | price  | ram
---------------------+--------+-----
 MacBook Air         | 999.99 | 8G
 Google Galaxy Nexus |  99.99 | 2G

CREATE INDEX item_ram ON items ((items.attributes->'ram'));

UPDATE items SET attributes = attributes || '"ram"=>"4G"'::hstore WHERE attributes->'ram' = '8G';

UPDATE items SET attributes = delete(attributes, 'osx_version');

SELECT * FROM items WHERE attributes @> '"ram"=>"2G"'::hstore;

 id |        name         | price |              attributes
----+---------------------+-------+---------------------------------------
  2 | Google Galaxy Nexus | 99.99 | "ram"=>"2G", "android_version"=>"4.3"

Important Notes

Resources

JSON

CREATE TABLE reports (
   id serial PRIMARY KEY,   name varchar,   data json);

INSERT INTO reports (name, data) VALUES (
    'NoMethodError',
    '{"backtrace": [         "sidekiq-2.12.0/lib/sidekiq/exception_handler.rb:21",         "sidekiq-2.12.0/lib/sidekiq/exception_handler.rb:12"       ],        "action": "create",         "controller": "cases"}'
);

INSERT INTO reports (name, data) VALUES (
   'SystemStackError',
   '{"backtrace": ["redis-3.0.4/lib/redis/client.rb:306"],    "action": "update", "controller": "clients"}');

SELECT id,data FROM reports;

 id |                              data
----+----------------------------------------------------------------
  1 | {"backtrace": [                                               +
    |          "sidekiq-2.12.0/lib/sidekiq/exception_handler.rb:21",+
    |          "sidekiq-2.12.0/lib/sidekiq/exception_handler.rb:12" +
    |        ],                                                     +
    |         "action": "create",                                   +
    |          "controller": "cases"}
  2 | {"backtrace": ["redis-3.0.4/lib/redis/client.rb:306"],        +
    |    "action": "update", "controller": "clients"}

SELECT id,name FROM reports WHERE data->>'controller' = 'clients';

 id |       name
----+------------------
  2 | SystemStackError

SELECT name, data->'controller' AS controller, data->'action' AS action FROM reports;

       name       | controller |  action
------------------+------------+----------
 NoMethodError    | "cases"    | "create"
 SystemStackError | "clients"  | "update"

Important Notes

Resources

Other Cool Stuff

Postgres Weekly