Is there some way to store EITHER a specific day (date) OR a year in the same column? I have a column called &quot;date or year of birth&quot;. The reason it's not called &quot;date of birth&quot; 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 &quot;1998&quot; in a date column, I get an error; PostgreSQL doesn't let me store it.
I would have to turn it into &quot;1998-01-01&quot; 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 &quot;real&quot; or just another case of &quot;we only know the year&quot;.
If I turn this column into a text, it will store both &quot;1998&quot; and &quot;1995-04-06&quot;, but this makes it a PITA to deal with whenever I need to compare it time-wise. Furthermore, it &quot;feels wrong&quot; to store such information in a &quot;text&quot; column; I only use those as a last resort when PG simply doesn't have &quot;native knowledge&quot; of the kind of information contained within.
What can I do about this?
I would model this thusly:
CREATE TABLE ... (
   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.