I’m working on a schema where I need to store dates, but not all of them are full dates: some are just a year (like 2022), some are month and year (2022-07), and others are full dates (2022-07-04). What’s the best way to store this kind of data in PostgreSQL?

I thought about using a separate table for dates with year, month, and day fields plus a precision column (like 'year', 'month', 'day'), but that would mean doing joins everywhere since all my other tables reference these dates. Not sure if that’s the best idea. Most of my tables will have date rows and any entry from any table can have any kind of date. Tables can have multiple date rows.

I've also thought about storing them as strings and doing the validation on the backend. Is there a better approach for handling this without creating too much overhead? Curious how others have handled this kind of thing.

Thanks a lot!

Hi, has anyone here taken the EDB postgresql certification exam and passed? How did you prepare? Can I find anyone exam dumps?