Re: Blank, nullable date column rejected by psql

Rich Shepard wrote:
> Now I know to replace no dates with null I'll do so but I'm curious why this
> is needed.

NULL is a special "unknown" value in SQL. You can use it for all
data types to signal that a value is unknown or not available.

If you insert a string into a "date" column, PostgreSQL will try
to convert the string to a date with the type input function.
The type input function fails on an empty string, since it cannot
parse it into a valid "date" value.
This also applies to the empty string.

But NULL is always a possible value (unless the column definition
excludes it).

Used properly, NULL solves many problems.

Imagine you want to know how long in the future the date is.
If you use "next_contact - current_timestamp", and "next_contact"
is NULL, then the result of the operation will automatically be
NULL (unknown) as well. That is much better than any "zero" value
which would lead to an undesired result.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com