PostgreSQL Hstore - Speaker Deck

Slide 1

Slide 1 text

PostgreSQL hstore Codeaholics.hk - Tuesday 8th May 2012 @MatthewRudy

Slide 2

Slide 2 text

Jason Crane isn’t here

Slide 3

Slide 3 text

a Coder • has a name • has a location • has a github (optional) • has a twitter (optional)

Slide 4

Slide 4 text

NoSQL?

Slide 5

Slide 5 text

Mongo { "name": "Matthew Rudy", "location": "Hong Kong", "twitter": "@matthewrudy", "github": "matthewrudy" }

Slide 6

Slide 6 text

Split the location { "name": "Matthew Rudy", "location_id": ObjectId("abc"), "twitter": "@matthewrudy", "github": "matthewrudy" } { "_id": ObjectId("abc"), "name": "Hong Kong" }

Slide 7

Slide 7 text

Requirements Grow! • Coders have Projects • Coders have commits on Projects • Projects have Categories • How many commits does @MatthewRudy have on PHP projects?

Slide 8

Slide 8 text

Isn’t this all a bit relational?

Slide 9

Slide 9 text

NoSQL?

Slide 10

Slide 10 text

No, SQL!

Slide 11

Slide 11 text

PostgreSQL CREATE TABLE coders ( name text NOT NULL, location_id integer NOT NULL REFERENCES locations, github text, twitter text );

Slide 12

Slide 12 text

Optional Fields • ('matthewrudy', '@matthewrudy') • ('matthewrudy', NULL) • (NULL, '@matthewrudy') • (NULL, NULL)

Slide 13

Slide 13 text

Find MatthewRudy on Github • SELECT * FROM coders WHERE github = “matthewrudy”;

Slide 14

Slide 14 text

How many Coders have Github accounts? • SELECT COUNT(*) FROM coders WHERE github IS NOT NULL;

Slide 15

Slide 15 text

How many Coders have Github and Twitter? • SELECT COUNT(*) FROM coders WHERE github IS NOT NULL AND twitter IS NOT NULL;

Slide 16

Slide 16 text

How many Coders have Github OR Twitter? • SELECT COUNT(*) FROM coders WHERE github IS NOT NULL OR twitter IS NOT NULL;

Slide 17

Slide 17 text

How many HKers have Github and Twitter? • SELECT COUNT(*) FROM coders WHERE github IS NOT NULL AND twitter IS NOT NULL AND location_id = 2;

Slide 18

Slide 18 text

hstore! • CREATE EXTENSION hstore; • SELECT 'github=>"matthewrudy", twitter=>"@matthewrudy"'::hstore;

Slide 19

Slide 19 text

value of key • (column -> ‘key’)::text • (column -> ‘key’)::integer • (column -> ‘key’)::date

Slide 20

Slide 20 text

has key? • column ? key • column ?& ARRAY[‘key1’, ‘key2’] • column ?| ARRAY[‘key1’, ‘key2’]

Slide 21

Slide 21 text

Indexes • CREATE INDEX hcoders_identities ON hcoders USING GIST(identities);

Slide 22

Slide 22 text

PostgreSQL hstore CREATE TABLE hcoders ( name text NOT NULL, location_id integer NOT NULL REFERENCES locations, identities hstore );

Slide 23

Slide 23 text

Optional Fields • 'github=>"matthewrudy", 'twitter=>"@matthewrudy"' • 'github=>"matthewrudy"' • 'twitter=>"@matthewrudy"' • ''

Slide 24

Slide 24 text

Find MatthewRudy on Github • SELECT * FROM coders WHERE (identities -> ‘github’)::text = “matthewrudy”;

Slide 25

Slide 25 text

Find MatthewRudy on Github • SELECT * FROM coders WHERE identities @> ‘github=>matthewrudy’

Slide 26

Slide 26 text

How many Coders have Github accounts? • SELECT COUNT(*) FROM coders WHERE identities ? ‘github';

Slide 27

Slide 27 text

How many Coders have Github and Twitter? • SELECT COUNT(*) FROM coders WHERE identities ?& ARRAY[‘github’, ‘twitter’];

Slide 28

Slide 28 text

How many Coders have Github OR Twitter? • SELECT COUNT(*) FROM coders WHERE identities ?| ARRAY[‘github’, ‘twitter’];

Slide 29

Slide 29 text

How many HKers have Github and Twitter? • SELECT COUNT(*) FROM coders WHERE identities ?& ARRAY[‘github’, ‘twitter’] AND location_id = 2;