storing year in database

Question

What is the best PostgreSQL data type for year data, e.g., 2006 or 1847. TEXT, SMALLINT, DATE? Ideally, I'd like to be able to query that column with a second year-month-day column (in DATE format).

Solution

A year is an integer and even supports integer arithmetic in a meaningful way so text makes no sense. You don't have a month or day so date is right out the window. The fine manual has this to say about smallint:

The smallint type is generally only used if disk space is at a premium.

That leaves integer as a natural choice. If you're planning to combine this value with dates and timestamps, then interval might make sense as the date and time functions and operators have a good understanding of intervals.

So it depends on what you're planning on doing with this "year" and what it really is. Sounds like a simple integer is appropriate but you might have to muck around a bit to combine it with your "second year-month-day column" (a date column presumably) depending on how they need to work together.