Postgres has a variety of datatypes, in fact quite a few more than most other databases. Most commonly applications take advantage of the standard ones – integers, text, numeric, etc. Almost every application needs these basic types, the rarer ones may be needed less frequently. And while not needed on every application when you do need them they can be an extremely handy. So without further ado let’s look at some of these rarer but awesome types.

hstore

Yes, I’ve talked about this one before, yet still not enough people are using it. Of this list of datatypes this is one that could also have benefit for most if not all applications.

Hstore is a key-value store directly within Postgres. This means you can easily add new keys and values (optionally), without haveing to run a migration to setup new columns. Further you can still get great performance by using Gin and GiST indexes with them, which automatically index all keys and values for hstore.

It’s of note that hstore is an extension and not enabled by default. If you want the ins and outs of getting hands on with it, give the article on Postgres Guide a read.

Range types

If there is ever a time where you have two columns in your database with one being a from, another being a to, you probably want to be using range types. Range types are just that a set of ranges. A super common use of them is when doing anything with calendaring. The place where they really become useful is in their ability to apply constraints on those ranges. This means you can make sure you don’t have overlapping time issues, and don’t have to rebuild heavy application logic to accomplish it.

Timestamp with Timezone

Timestamps are annoying, plain and simple. If you’ve re-invented handling different timezones within your application you’ve wasted plenty of time and likely done it wrong. If you’re using plain timestamps within your application further there’s a good chance they dont even mean what you think they mean. Timestamps with timezone or timestamptz automatically includes the timezone with the timestamp. This makes it easy to convert between timezones, know exactly what you’re dealing with, and will in short save you a ton of time. There’s seldom a case you shouldn’t be using these.

UUID

Integers as primary keys aren’t great. Sure if you’re running a small blog they work fine, but if you’re application has to scale to a large size then integers can create problems. First you can run out of them, second it can make other details such as sharding a little more annoying. At the same time they are super readable. However, using the actual UUID datatype and extension to automatically generate them can be incredibly handy if you have to scale an application.

Similar to hstore, there’s an extension that makes the UUID much more useful.

Binary JSON

This isn’t available yet, but will be in Postgres 9.4. Binary JSON is of course JSON directly within your database, but also lets you add Gin indexes directly onto JSON. This means a much simpler setup in not only inserting JSON, but having fast reads. If you want to learn a bit more about this, sign up to get notified of training regarding the upcoming PostgreSQL 9.4 release.

Money

Please don’t use this… The money datatype assumes a single currency type, and generally brings with it more caveats than simply using a numeric type.

More

It’s already been pointed out on twitter that I missed a few. To give a quick highlight of some others:

• Arrays
• Interval – time intervals, such as ‘1 hour’, ‘1 day’
• ISN - should help for anything with products
• Inet - Tracking IPs

In conclusion

What’d I miss? What are you’re favorite types? Let me know @craigkerstiens, or sign-up below to updates on Postgres content and first access to training.