PostgreSQL – Define Column for Year Data Type

datatypesdate formatpostgresql

I want to save the year in a PostgreSQL database – just the year. How should I create this field? There is a lot of information out there how I can extract the year from a date field, but not on how I should set up a table for using a year only. I tried this:

CREATE TABLE information (
    id serial PRIMARY KEY,
    year date NOT NULL
);

INSERT INTO "information" ("year") VALUES (2010);

But this fails:

ERROR: invalid input syntax for type date: "2010"

Best Answer

Either use a simple integer and represent BC as negative:

CREATE TABLE information (
  id serial PRIMARY KEY,
  year integer NOT NULL
);

or use a date that's constrained to the 1st of Jan and use 2014-01-01 etc in input/output:

CREATE TABLE information (
    id serial PRIMARY KEY,
    year date NOT NULL,
    CONSTRAINT year_must_be_1st_jan CHECK ( date_trunc('year', year) = year )
);

Which is more appropriate depends mostly on how your app is using the information. I think it's OK to represent a year as a simple integer - just please don't do this for year integer, month integer. That's awful to work with, and is where a constrained date is more appropriate.

I tend to use constrained dates so that when the requirements inevitably change I can adapt more easily.

Related Question