Blank, nullable date column rejected by psql

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Blank, nullable date column rejected by psql

Rich Shepard
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


Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

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

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Adrian Klaver-4
In reply to this post by Rich Shepard
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Tom Lane-2
In reply to this post by Rich Shepard
Rich Shepard <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

David G Johnston
In reply to this post by Rich Shepard
On Mon, Feb 11, 2019 at 3:44 PM Rich Shepard <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Rich Shepard
In reply to this post by Tom Lane-2
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


Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Rich Shepard
In reply to this post by Ron-2
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

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Ron-2
In reply to this post by Rich Shepard
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.

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Rich Shepard
In reply to this post by Rich Shepard
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

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Rich Shepard
In reply to this post by Ron-2
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

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Ken Tanzer
In reply to this post by Rich Shepard

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
(253) 245-3801

learn more about AGENCY or
follow the discussion.
Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Rich Shepard
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


Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Ken Tanzer

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
(253) 245-3801

learn more about AGENCY or
follow the discussion.
Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Ron-2
In reply to this post by Rich Shepard
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.

Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Laurenz Albe
In reply to this post by Rich Shepard
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


Reply | Threaded
Open this post in threaded view
|

Re: Blank, nullable date column rejected by psql

Rich Shepard
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