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
210
Humans are Hard
matthewrudy
0
110
[Alpha] Humans Are Hard
matthewrudy
0
85
From Developer To Architect
matthewrudy
0
63
Git Commit Signing: Code we can trust?
matthewrudy
0
150
We Need To Talk About Postgres
matthewrudy
0
72
Coding as a Team At GoGoVan
matthewrudy
3
400
10 Years of Code
matthewrudy
0
95
Elixir - Part 1
matthewrudy
1
160
Other Decks in Technology
See All in Technology
自己改善からチームを動かす! 「セルフエンジニアリングマネージャー」のすゝめ
shoota
6
1.1k
ルーターでプレゼンする
puhitaku
1
3.4k
Gitlab本から学んだこと - そーだいなるプレイバック / gitlab-book
soudai
7
1.4k
M&A戦略を支えるデータマネジメント (MIDAS Tech Study #16 GENDA Komiyama)
kommy339
1
140
LayerXにおけるLLMプロダクト開発の今までとこれから
layerx
PRO
4
790
MLOpsの「壁」を乗り越える、LINEヤフーの Data Quality as Code
lycorptech_jp
PRO
8
660
チームでロジカルシンキングに改めて向き合っている話 〜学習環境と実践⽅法〜
sansantech
PRO
3
3.3k
LangSmith入門―トレース/評価/プロンプト管理などを担うLLMアプリ開発プラットフォーム
os1ma
5
770
コードや知識を組み込む / Incorporate Code and knowledge
ks91
PRO
0
150
ExaDB-D dbaascli で出来ること
oracle4engineer
PRO
0
2.1k
Gradle Build Scanを使ってビルドのことを知ろう potatotips #87
tomorrowkey
2
160
Zero Data Loss Autonomous Recovery Service サービス概要
oracle4engineer
PRO
0
1.9k
Featured
See All Featured
It's Worth the Effort
3n
180
27k
Why You Should Never Use an ORM
jnunemaker
PRO
51
8.7k
The Illustrated Children's Guide to Kubernetes
chrisshort
32
46k
Large-scale JavaScript Application Architecture
addyosmani
504
110k
Building Applications with DynamoDB
mza
88
5.6k
VelocityConf: Rendering Performance Case Studies
addyosmani
321
23k
In The Pink: A Labor of Love
frogandcode
138
21k
The Cost Of JavaScript in 2023
addyosmani
21
3.9k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
79
43k
RailsConf 2023
tenderlove
9
560
Web Components: a chance to create the future
zenorocha
306
41k
Become a Pro
speakerdeck
PRO
13
4.6k
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