Thread: Blank, nullable date column rejected by psql

Running postgresql-10.5 on Slackware-14.2.

A table has a column defined as

Column       | Type  | Collation | Nullable | Default
next_contact | date  |           |          |

In a .sql file to insert rows in this table psql has a problem when there's
no value for the next_contact column:

$ psql -f activities.sql -d bustrac 
psql:activities.sql:6: ERROR:  invalid input syntax for type date: ""
LINE 2: ...ise. Asked him to call.',''),

Explicitly replacing the blank field ('') with null is accepted. Why is
this?

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

Thanks in advance,

Rich

On 2/11/19 4:44 PM, Rich Shepard wrote:
> Running postgresql-10.5 on Slackware-14.2.
>
> A table has a column defined as
>
> Column       | Type  | Collation | Nullable | Default
> next_contact | date  |           |          |
>
> In a .sql file to insert rows in this table psql has a problem when there's
> no value for the next_contact column:
>
> $ psql -f activities.sql -d bustrac psql:activities.sql:6: ERROR: invalid 
> input syntax for type date: ""
> LINE 2: ...ise. Asked him to call.',''),
>
> Explicitly replacing the blank field ('') with null is accepted. Why is
> this?
>
> Now I know to replace no dates with null I'll do so but I'm curious why this
> is needed.

NULL is nothing.  Blank isn't nothing; blank is a zero-length string.  Thus, 
you need to tell Pg "nothing", not "blank string".

(Oracle is really bad about that.)

-- 
Angular momentum makes the world go 'round.

On 2/11/19 2:44 PM, Rich Shepard wrote:
> Running postgresql-10.5 on Slackware-14.2.
> 
> A table has a column defined as
> 
> Column       | Type  | Collation | Nullable | Default
> next_contact | date  |           |          |
> 
> In a .sql file to insert rows in this table psql has a problem when there's
> no value for the next_contact column:
> 
> $ psql -f activities.sql -d bustrac psql:activities.sql:6: ERROR:  
> invalid input syntax for type date: ""
> LINE 2: ...ise. Asked him to call.',''),
> 
> Explicitly replacing the blank field ('') with null is accepted. Why is
> this?
> 
> Now I know to replace no dates with null I'll do so but I'm curious why 
> this
> is needed.

Because:

invalid input syntax for type date: ""

means you are trying to enter an empty string("") and that:

a) Is not NULL
b) Is not a valid date string.

> 
> Thanks in advance,
> 
> Rich
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Rich Shepard <rshepard@appl-ecosys.com> writes:
> In a .sql file to insert rows in this table psql has a problem when there's
> no value for the next_contact column:

> $ psql -f activities.sql -d bustrac 
> psql:activities.sql:6: ERROR:  invalid input syntax for type date: ""
> LINE 2: ...ise. Asked him to call.',''),

> Explicitly replacing the blank field ('') with null is accepted. Why is
> this?

An empty string is not a null.

(Oracle has done untold damage to the field by failing to make this
distinction ... but in theory, and in the SQL standard, and in Postgres,
they're not at all the same thing.)

            regards, tom lane

On Mon, Feb 11, 2019 at 3:44 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
> Now I know to replace no dates with null I'll do so but I'm curious why this
> is needed.

Same reason you needed it about a month ago when you were dealing with
a check constraint question with the same error message.

David J.

On Mon, 11 Feb 2019, Tom Lane wrote:

> An empty string is not a null.

Tom,

I understand this yet thought that empty strings and numeric fields were
accepted. Guess I need to review this.

Thanks,

Rich

On Mon, 11 Feb 2019, Ron wrote:

> NULL is nothing. Blank isn't nothing; blank is a zero-length string. 
> Thus, you need to tell Pg "nothing", not "blank string".

Ron,

All of you who responded drove home my need to explicitly enter null when
there are no data for a column.

Thanks,

Rich

On 2/11/19 5:30 PM, Rich Shepard wrote:
> On Mon, 11 Feb 2019, Tom Lane wrote:
>
>> An empty string is not a null.
>
> Tom,
>
> I understand this yet thought that empty strings and numeric fields were
> accepted. Guess I need to review this.

You've got ADOS (All Databases are Oracle Syndrome).

-- 
Angular momentum makes the world go 'round.

On Mon, 11 Feb 2019, Rich Shepard wrote:

> All of you who responded drove home my need to explicitly enter null when
> there are no data for a column.

Correction: when there are no date data for a column.

Rich

On Mon, 11 Feb 2019, Ron wrote:

> You've got ADOS (All Databases are Oracle Syndrome).

Interesting as I've never bought, used, or seen anything from Oracle. Guess
it's transmitted by errent bits.

Regards,

Rich

On Mon, 11 Feb 2019, Ken Tanzer wrote:

> Just in case you miss this little nuance, you don't necessarily _have_ to
> specify a NULL for that column, depending how you're doing your inserts.
> You haven't show us your table or what INSERT you're using, but all of
> these examples will work, and don't specify an explicit NULL:

Ken,

Well, you've succeeded in confusing me. :-)

This is the table's schema:

# \d activities
                                Table "public.activities"
     Column    |         Type          | Collation | Nullable |  Default 
--------------+-----------------------+-----------+----------+-------------
------------
  person_id    | integer               |           | not null |
  act_date     | date                  |           | not null | CURRENT_DATE
  act_type     | character varying(12) |           | not null | '??'::charac
ter varying
  notes        | text                  |           | not null | '??'::text
  next_contact | date                  |           |          | 
Indexes:
     "activities_pkey" PRIMARY KEY, btree (person_id, act_date, act_type)
Foreign-key constraints:
     "activities_act_type_fkey" FOREIGN KEY (act_type) REFERENCES activityty
pes(act_name) ON UPDATE CASCADE ON DELETE RESTRICT
     "activities_person_id_fkey" FOREIGN KEY (person_id) REFERENCES people(p
erson_id) ON UPDATE CASCADE ON DELETE RESTRICT

And this is the framwork for adding rows:

insert into Activities (person_id,act_date,act_type,notes,next_contact) values
(

I add values for each column, but if there's no scheduled next_contact date
I left that off. To me, this looks like your second example (with two
columns of values and no date) and I don't see the differences.

Regards,

Rich

On 2/11/19 5:44 PM, Rich Shepard wrote:
> On Mon, 11 Feb 2019, Ron wrote:
>
>> You've got ADOS (All Databases are Oracle Syndrome).
>
> Interesting as I've never bought, used, or seen anything from Oracle. Guess
> it's transmitted by errent bits.

It's easily transmitted via toilet seats.

-- 
Angular momentum makes the world go 'round.

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

On Tue, 12 Feb 2019, Laurenz Albe wrote:

> 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.

Laurenz,

All my previous databases with date columns were required to have an entry
because each row contatined spatio-temporal sampling data. The table in this
business-oriented application is not required to have a next_contact date
and (the crucial point) is that I conflated date values with string values.
As you wrote, a date column is converted from the entered string to a
non-string date type and requires an explicit null when there is no value to
be stored for that column.

Thanks very much,

Rich