I’m working with an AWS RDS Postgres database, and noticed a bunch of errors about date fields with the value of 0001-01-01. After some investigation, I found something puzzling with the behavior of AWS RDS for Postgres that I’m hoping someone can clarify.

In a standard Postgres DB, if you execute to_date with either a null or an empty string, it will evaluate to a date null. I believe that technically, internal to Postgres this is stored as the date 01/01/0001 00:00:00. The important thing about this date is that I can select its value with is null. I imagine that this value will display differently depending on the client, but in all of the clients I’m using it just shows up as a blank value. Not null, not an empty string, just no content.

In my RDS for Postgres DB however, executing to_date on a null or an empty string produces the explicit date 0001-01-01. This date value is not selectable via is null as far as I can tell.

This seems like a pretty significant difference between the DB engines, and makes me worry about what other differences I will encounter. Is there maybe a way to get RDS for Postgres to use the standard date behavior, or do I just need to account for these things?