CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03

Fight!

But more seriously – people tend to use various data types, and there have been some myths about them, so let's see how it really boils down.

First of all – All those data types are internally saved using the same C data structure – varlena.

Thanks to this we can be nearly sure that there are no performance differences. Are there no performance differences in reality? Let's test.

Continue reading CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03

Getting list of most common domains

Today, on #postgresql on IRC, guy (can't contact him now to get his permission to name him), said:

I have a table called problematic_hostnames. It contains a list of banned hostnames in column “hostname" (varchar). I would like to display the top 10 troll ISPs based on this. Does PG have a way of spotting a “pattern"? Some ISPs are example.net while others are foo.bar.example.net, so you can't just regexp the last X.Y (since that would cause “.co.uk" to be one of the top troll ISPs).

Continue reading Getting list of most common domains

Text comparisons that does automatic trim()

SoftNum asked on irc:

< SoftNum> does postgresql have a config option to automatically trim (both ' ' from blah) on string compares?

So, can you?

Of course there is no such option, but maybe there is a way to tell PostgreSQL to do this trim for given field? Sure there is 🙂

Continue reading Text comparisons that does automatic trim()