31

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).

metasequoia
  • 7,014
  • 5
  • 41
  • 54

1 Answers1

44

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 13
    Premium or not... why use integer if you can use smallint? Smallint can represent values up to 32767. In other words, your system will have 30 milleniums before this becomes an issue. Go with smallint. – sam yi Dec 11 '12 at 21:14
  • 2
    @sam: There could be a run-time cost to using a 2 byte type when most machines are happier with things aligned on 4 or 8 byte boundaries. Go with `integer` unless you have a very good reason to worry about a couple extra bytes on disk. Generally you're better off going with whatever sizes are the most native to the machine in question. – mu is too short Dec 11 '12 at 23:57
  • 7
    this smallint to int conversion is negligible as far as performance. however if you looking at a very wide table, storage savings plus smaller indexes can make it worth while to choose smallint over int. but in either case... i think the difference is so small that it's hardly worth arguing over... go with integer. – sam yi Dec 12 '12 at 01:32