Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
PostgreSQL Hstore
Search
Matthew Rudy Jacobs
May 09, 2012
Technology
8
1.8k
PostgreSQL Hstore
Talk at Codeaholics Hong Kong
Wednesday 9th May 2012
Matthew Rudy Jacobs
May 09, 2012
Tweet
Share
More Decks by Matthew Rudy Jacobs
See All by Matthew Rudy Jacobs
From Developer to Architect (and back again)
matthewrudy
3
230
Humans are Hard
matthewrudy
0
140
[Alpha] Humans Are Hard
matthewrudy
0
95
From Developer To Architect
matthewrudy
0
75
Git Commit Signing: Code we can trust?
matthewrudy
0
180
We Need To Talk About Postgres
matthewrudy
0
88
Coding as a Team At GoGoVan
matthewrudy
3
430
10 Years of Code
matthewrudy
0
110
Elixir - Part 1
matthewrudy
1
190
Other Decks in Technology
See All in Technology
AWSで推進するデータマネジメント
kawanago
0
730
「AI2027」を紐解く ― AGI・ASI・シンギュラリティ
masayamoriofficial
0
160
生成AI時代のデータ基盤設計〜ペースレイヤリングで実現する高速開発と持続性〜 / Levtech Meetup_Session_2
sansan_randd
1
100
攻撃と防御で実践するプロダクトセキュリティ演習~導入パート~
recruitengineers
PRO
3
1.7k
ヘブンバーンズレッドにおける、世界観を活かしたミニゲーム企画の作り方
gree_tech
PRO
0
410
サポートエンジニアから見たRancher運用の現場
masap
0
110
実践アプリケーション設計 ①データモデルとドメインモデル
recruitengineers
PRO
5
1.4k
実践AIガバナンス
asei
3
270
Webアクセシビリティ入門
recruitengineers
PRO
3
1.4k
『FailNet~やらかし共有SNS~』エレベーターピッチ
yokomachi
1
190
サンドボックス技術でAI利活用を促進する
koh_naga
0
120
個人CLAUDE.md紹介と設定から学んだこと/introduce-my-claude-md
shibayu36
0
140
Featured
See All Featured
RailsConf 2023
tenderlove
30
1.2k
The Art of Programming - Codeland 2020
erikaheidi
55
13k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
185
54k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
32
1.5k
How STYLIGHT went responsive
nonsquared
100
5.8k
Optimising Largest Contentful Paint
csswizardry
37
3.4k
Java REST API Framework Comparison - PWX 2021
mraible
33
8.8k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
addyosmani
7
830
Typedesign – Prime Four
hannesfritz
42
2.8k
Documentation Writing (for coders)
carmenintech
73
5k
Raft: Consensus for Rubyists
vanstee
140
7.1k
GitHub's CSS Performance
jonrohan
1032
460k
Transcript
PostgreSQL hstore Codeaholics.hk - Tuesday 8th May 2012 @MatthewRudy
Jason Crane isn’t here
a Coder • has a name • has a location
• has a github (optional) • has a twitter (optional)
NoSQL?
Mongo { "name": "Matthew Rudy", "location": "Hong Kong", "twitter": "@matthewrudy",
"github": "matthewrudy" }
Split the location { "name": "Matthew Rudy", "location_id": ObjectId("abc"), "twitter":
"@matthewrudy", "github": "matthewrudy" } { "_id": ObjectId("abc"), "name": "Hong Kong" }
Requirements Grow! • Coders have Projects • Coders have commits
on Projects • Projects have Categories • How many commits does @MatthewRudy have on PHP projects?
Isn’t this all a bit relational?
NoSQL?
No, SQL!
PostgreSQL CREATE TABLE coders ( name text NOT NULL, location_id
integer NOT NULL REFERENCES locations, github text, twitter text );
Optional Fields • ('matthewrudy', '@matthewrudy') • ('matthewrudy', NULL) • (NULL,
'@matthewrudy') • (NULL, NULL)
Find MatthewRudy on Github • SELECT * FROM coders WHERE
github = “matthewrudy”;
How many Coders have Github accounts? • SELECT COUNT(*) FROM
coders WHERE github IS NOT NULL;
How many Coders have Github and Twitter? • SELECT COUNT(*)
FROM coders WHERE github IS NOT NULL AND twitter IS NOT NULL;
How many Coders have Github OR Twitter? • SELECT COUNT(*)
FROM coders WHERE github IS NOT NULL OR twitter IS NOT NULL;
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;
hstore! • CREATE EXTENSION hstore; • SELECT 'github=>"matthewrudy", twitter=>"@matthewrudy"'::hstore;
value of key • (column -> ‘key’)::text • (column ->
‘key’)::integer • (column -> ‘key’)::date
has key? • column ? key • column ?& ARRAY[‘key1’,
‘key2’] • column ?| ARRAY[‘key1’, ‘key2’]
Indexes • CREATE INDEX hcoders_identities ON hcoders USING GIST(identities);
PostgreSQL hstore CREATE TABLE hcoders ( name text NOT NULL,
location_id integer NOT NULL REFERENCES locations, identities hstore );
Optional Fields • 'github=>"matthewrudy", 'twitter=>"@matthewrudy"' • 'github=>"matthewrudy"' • 'twitter=>"@matthewrudy"' •
''
Find MatthewRudy on Github • SELECT * FROM coders WHERE
(identities -> ‘github’)::text = “matthewrudy”;
Find MatthewRudy on Github • SELECT * FROM coders WHERE
identities @> ‘github=>matthewrudy’
How many Coders have Github accounts? • SELECT COUNT(*) FROM
coders WHERE identities ? ‘github';
How many Coders have Github and Twitter? • SELECT COUNT(*)
FROM coders WHERE identities ?& ARRAY[‘github’, ‘twitter’];
How many Coders have Github OR Twitter? • SELECT COUNT(*)
FROM coders WHERE identities ?| ARRAY[‘github’, ‘twitter’];
How many HKers have Github and Twitter? • SELECT COUNT(*)
FROM coders WHERE identities ?& ARRAY[‘github’, ‘twitter’] AND location_id = 2;
Available on Heroku PG9.1 beta now!
@matthewrudy Please don’t stalk me!
Codeaholics http://groups.google.com/group/codeaholics