PostgreSQL Hstore

Talk at Codeaholics Hong Kong
Wednesday 9th May 2012

Transcript

1. PostgreSQL hstore Codeaholics.hk - Tuesday 8th May 2012 @MatthewRudy
2. Jason Crane isn’t here
3. a Coder • has a name • has a location • has a github (optional) • has a twitter (optional)
4. NoSQL?
5. Mongo { "name": "Matthew Rudy", "location": "Hong Kong", "twitter": "@matthewrudy", "github": "matthewrudy" }
6. Split the location { "name": "Matthew Rudy", "location_id": ObjectId("abc"), "twitter": "@matthewrudy", "github": "matthewrudy" } { "_id": ObjectId("abc"), "name": "Hong Kong" }
7. Requirements Grow! • Coders have Projects • Coders have commits on Projects • Projects have Categories • How many commits does @MatthewRudy have on PHP projects?
8. Isn’t this all a bit relational?
9. NoSQL?
10. No, SQL!
11. PostgreSQL CREATE TABLE coders ( name text NOT NULL, location_id integer NOT NULL REFERENCES locations, github text, twitter text );
12. Optional Fields • ('matthewrudy', '@matthewrudy') • ('matthewrudy', NULL) • (NULL, '@matthewrudy') • (NULL, NULL)
13. Find MatthewRudy on Github • SELECT * FROM coders WHERE github = “matthewrudy”;
14. How many Coders have Github accounts? • SELECT COUNT(*) FROM coders WHERE github IS NOT NULL;
15. How many Coders have Github and Twitter? • SELECT COUNT(*) FROM coders WHERE github IS NOT NULL AND twitter IS NOT NULL;
16. How many Coders have Github OR Twitter? • SELECT COUNT(*) FROM coders WHERE github IS NOT NULL OR twitter IS NOT NULL;
17. 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;
18. hstore! • CREATE EXTENSION hstore; • SELECT 'github=>"matthewrudy", twitter=>"@matthewrudy"'::hstore;
19. value of key • (column -> ‘key’)::text • (column -> ‘key’)::integer • (column -> ‘key’)::date
20. has key? • column ? key • column ?& ARRAY[‘key1’, ‘key2’] • column ?| ARRAY[‘key1’, ‘key2’]
21. Indexes • CREATE INDEX hcoders_identities ON hcoders USING GIST(identities);
22. PostgreSQL hstore CREATE TABLE hcoders ( name text NOT NULL, location_id integer NOT NULL REFERENCES locations, identities hstore );
23. Optional Fields • 'github=>"matthewrudy", 'twitter=>"@matthewrudy"' • 'github=>"matthewrudy"' • 'twitter=>"@matthewrudy"' • ''
24. Find MatthewRudy on Github • SELECT * FROM coders WHERE (identities -> ‘github’)::text = “matthewrudy”;
25. Find MatthewRudy on Github • SELECT * FROM coders WHERE identities @> ‘github=>matthewrudy’
26. How many Coders have Github accounts? • SELECT COUNT(*) FROM coders WHERE identities ? ‘github';
27. How many Coders have Github and Twitter? • SELECT COUNT(*) FROM coders WHERE identities ?& ARRAY[‘github’, ‘twitter’];
28. How many Coders have Github OR Twitter? • SELECT COUNT(*) FROM coders WHERE identities ?| ARRAY[‘github’, ‘twitter’];
29. How many HKers have Github and Twitter? • SELECT COUNT(*) FROM coders WHERE identities ?& ARRAY[‘github’, ‘twitter’] AND location_id = 2;
30. Available on Heroku PG9.1 beta now!
31. @matthewrudy Please don’t stalk me!
32. Codeaholics http://groups.google.com/group/codeaholics