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
130
[Alpha] Humans Are Hard
matthewrudy
0
93
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
Claude Codeから我々が学ぶべきこと
oikon48
10
2.8k
ロールが細分化された組織でSREと協働するインフラエンジニアは何をするか? / SRE Lounge #18
kossykinto
0
220
JAWS AI/ML #30 AI コーディング IDE "Kiro" を触ってみよう
inariku
3
360
LTに影響を受けてテンプレリポジトリを作った話
hol1kgmg
0
360
Agent Development Kitで始める生成 AI エージェント実践開発
danishi
0
150
バクラクによるコーポレート業務の自動運転 #BetAIDay
layerx
PRO
1
950
【CEDEC2025】『Shadowverse: Worlds Beyond』二度目のDCG開発でゲームをリデザインする~遊びやすさと競技性の両立~
cygames
PRO
1
370
Strands Agents & Bedrock AgentCoreを1分でおさらい
minorun365
PRO
7
320
OPENLOGI Company Profile for engineer
hr01
1
38k
僕たちが「開発しやすさ」を求め 模索し続けたアーキテクチャ #アーキテクチャ勉強会_findy
bengo4com
0
2.4k
Amazon Q Developerを活用したアーキテクチャのリファクタリング
k1nakayama
2
210
20250807_Kiroと私の反省会
riz3f7
0
220
Featured
See All Featured
Facilitating Awesome Meetings
lara
54
6.5k
Intergalactic Javascript Robots from Outer Space
tanoku
272
27k
Build The Right Thing And Hit Your Dates
maggiecrowley
37
2.8k
Making Projects Easy
brettharned
117
6.3k
Bash Introduction
62gerente
614
210k
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
Designing for humans not robots
tammielis
253
25k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
332
22k
VelocityConf: Rendering Performance Case Studies
addyosmani
332
24k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
1.8k
A Tale of Four Properties
chriscoyier
160
23k
Speed Design
sergeychernyshev
32
1.1k
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