Skip site navigation (1) Skip section navigation (2)

meeting recap and the next meeting!

From: Selena Deckelmann <sdeckelmann(at)chrisking(dot)com>
To: pdxpug(at)postgresql(dot)org
Subject: meeting recap and the next meeting!
Date: 2007-02-22 21:22:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pdxpug

Welcome to yet another edition of "what happened at PDXPUG".

Slides and SQL from the presentation are at: http://

I apologize to those of you who asked for an audio recording. I was  
unable to produce a podcast of David's talk on DOMAINs. Everything  
seemed to be ok, but at the crucial moment, the program I used seemed  
to eat the audio.  Maybe next time Randal will let us use his  
recording setup with his iPod.

But onward!
Randal's brother Russ came to the meeting, so we were treated to the  
story of their conceptions.

The EXPLAIN Operator Of The Month was: Index Scan.  I ran through an  
example of Index Scanning on a text column, showing the difference  
between using a B-Tree or Hash index, and a situation (equality  
tests) where the Hash Index performs quite a bit better. But, when  
using LIKE, the B-Tree Index far outperformed the Hash Index. In  
creating my vi 'presentation', I learned that you can have multiple  
indexes on the same column (and the planner will chose the most  
appropriate index for a query), and about functional and partial  
indexes (which are really cool).  David Wheeler mentioned creating  
partial indexes on columns that tend to have lots of NULL values, and  
also another example where indexing NULL values in a column is useful.

David's presentation on DOMAINs started with a common problem in  
databases - a 'state' variable that has the same constraint, and  
exists in multiple tables.  After creating table after table with the  
same CONSTRAINT, David implored us to stop the madness.  Use a  
DOMAIN!  When constraints on a DOMAIN prevent an INSERT, the error  
message is very similar.

After that example, David showed how DOMAINs really kick ass.  This  
more complex example validated UPC/EAN/GTIN values - GTIN is a  
superset of UPC/EAN.  DOMAINs can be validated by arbitrary  
functions!  David showed the code for this and demonstrated the use  
of generate_series() in a fancy isa_gtin() function to verify the  
checksum on GTINs.

DOMAINs pose problems with sorting.  The GTINs, for example, is  
fundamentally on a text field and may contain different numbers of  
digits, which causes numeric sorts to fail on.  To fix this, David  
showed how to override operators (well, that's what I would call it  
anyway).  To produce indexes for the DOMAIN, you must create an  
operator class - so he had examples for every operator needed to  
create this. ORDER BY also uses the operators, as long as you specify  
explicitly the operator you'd like to use.  All the functions are in  
the SQL file linked on the PDXPUG webpage.

We did talk about a datatype in contrib/ that does all/most of the  
work for you in C - the isbn_issn extension is what you are looking for.

And that was it!

Randal agreed to give a presentation on removing inheritance from a  
database.  This will be on March 20th, 7pm.  Gabrielle will be your  
fearless leader for this meeting.  I will be vacationing in Germany  
for a couple of weeks in March!

After the meeting, we retired to the Lucky Lab where we drank a  
couple pitchers and I asked some dumb questions about Object Oriented  

Thanks to everyone who attended.



pdxpug by date

Next:From: Selena DeckelmannDate: 2007-02-22 21:59:37
Subject: Re: meeting recap and the next meeting!
Previous:From: Selena DeckelmannDate: 2007-02-20 16:07:07
Subject: TODAY! DOMAINs with David Wheeler

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group