I have a column called "date or year of birth". The reason it's not called "date of birth" is that, in some cases, the exact day is not known -- only the year, or only the year and month.

If I try to store "1998" in a date column, I get an error; PostgreSQL doesn't let me store it.

I would have to turn it into "1998-01-01" for it to be allowed to be stored, but now it's probably lying (unless they happen to be born on 1st of January 1998).

Simply having only the year is not the same as 1st of January 1998. This way, I cannot ever be sure if birth dates on that day are "real" or just another case of "we only know the year".

If I turn this column into a text, it will store both "1998" and "1995-04-06", but this makes it a PITA to deal with whenever I need to compare it time-wise. Furthermore, it "feels wrong" to store such information in a "text" column; I only use those as a last resort when PG simply doesn't have "native knowledge" of the kind of information contained within.

What can I do about this?

  • Store the year and the day of the year (when available) separately, as integers? – mustaccio Jun 26 at 11:57

I would model this thusly:

   birthday date NOT NULL,
   only_year boolean DEFAULT FALSE NOT NULL,

The second column indicates if only the year of the data is relevant or not.

| improve this answer | |
  • 1
    My prefered way too. It may even be extended to a column defining the granularity/precision of birthday. Ex 0 full date, 1 year_month, 2 year, 3 decennium and so forth – Lennart Jun 26 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.