Please read also about this change in Pg 9.1, and this change in Pg 9.2 posts, as they explain that since Pg 9.1 some of the limitations listed in this post are no longer there.
END OF UPDATE
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
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
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()
MySQL has this nifty/annoying feature/bug of special data type “TIMESTAMP". It is like a DATETIME, but it gets automatically updated whenever you modify the row.
I'll try to add the same feature to PostgreSQL.
Continue reading MySQL's timestamp in PostgreSQL