Blank, nullable date column rejected by psql

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

Ron,

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

Thanks,

Rich

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:

CREATE TEMP TABLE foo (a INTEGER NOT NULL, b INTEGER NOT NULL, c DATE);
CREATE TABLE

INSERT INTO foo VALUES (1,2);
INSERT 0 1
^
INSERT INTO foo (a,b) VALUES (1,2);
INSERT 0 1

INSERT INTO foo (a,b) SELECT 1,2;
INSERT 0 1

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

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

Ken,

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

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.

Assuming you're meaning this example:

INSERT INTO foo (a,b) VALUES (1,2);

The difference is I didn't specify field c in the list of columns, so it
gets inserted with its default value. That would be the same as:

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

Now that will work if you're doing a separate INSERT for each row. If
you're doing multiple VALUES in one select, and some have a next contact
date and some don't, then I think you're going to need to explicitly spell
out your NULLs.

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

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