storing year in database

database, database-design, postgresql, postgresql-9.1

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 `date`s and `timestamps`, then `interval` might make sense as the date and time functions and operators have a good understanding of `interval`s.

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.

Problem

Original source