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
「何となくテストする」を卒業するためにプロダクトが動く仕組みを理解しよう
kawabeaver
0
430
Snowflake Intelligenceにはこうやって立ち向かう!クラシルが考えるAI Readyなデータ基盤と活用のためのDataOps
gappy50
0
280
Autonomous Database - Dedicated 技術詳細 / adb-d_technical_detail_jp
oracle4engineer
PRO
4
10k
エンジニアが主導できる組織づくり ー 製品と事業を進化させる体制へのシフト
ueokande
1
100
株式会社ログラス - 会社説明資料【エンジニア】/ Loglass Engineer
loglass2019
4
65k
複数サービスを支えるマルチテナント型Batch MLプラットフォーム
lycorptech_jp
PRO
1
950
現場で効くClaude Code ─ 最新動向と企業導入
takaakikakei
1
260
MagicPod導入から半年、オープンロジQAチームで実際にやったこと
tjoko
0
110
Modern Linux
oracle4engineer
PRO
0
160
スマートファクトリーの第一歩 〜AWSマネージドサービスで 実現する予知保全と生成AI活用まで
ganota
2
320
[ JAWS-UG 東京 CommunityBuilders Night #2 ]SlackとAmazon Q Developerで 運用効率化を模索する
sh_fk2
3
460
自作JSエンジンに推しプロポーザルを実装したい!
sajikix
1
190
Featured
See All Featured
Large-scale JavaScript Application Architecture
addyosmani
513
110k
Into the Great Unknown - MozCon
thekraken
40
2k
The Pragmatic Product Professional
lauravandoore
36
6.9k
4 Signs Your Business is Dying
shpigford
184
22k
Mobile First: as difficult as doing things right
swwweet
224
9.9k
Navigating Team Friction
lara
189
15k
The Cult of Friendly URLs
andyhume
79
6.6k
Keith and Marios Guide to Fast Websites
keithpitt
411
22k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
234
17k
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
BBQ
matthewcrist
89
9.8k
Building a Modern Day E-commerce SEO Strategy
aleyda
43
7.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