Thread: How do I COPY empty Datetimes?

To whoever is reading this mail for the second time, excuse me for the
cross-postings... I've already asked in 'general', since I've not gotten
any response I'm begging you guys in here to give me some advice...

------
Hi all. I have a problem here. I have to automatize the periodical COPY
of a large set of ASCII data in a postgres database. The parser digests
everything I put in there, with an exception: if there is a DateTime
field that is empty, the copy of the field fails for a "Invalid datetime
format". The field isn't a NOT NULL one, so I have to accept also empty
values... I've tried with the sequence '', "", or simply /t/t (tab is
the field separator in those ASCII data), but the output has always been
the same.

I would be immensely grateful to any 1 that could shed some light...
private responses please, I've had to unsubscribe... ya know, the phone
bill!!

TIA
-----------

Fabrizio Ermini

f.ermini@telemaco.it writes:
> Hi all. I have a problem here. I have to automatize the periodical COPY
> of a large set of ASCII data in a postgres database. The parser digests
> everything I put in there, with an exception: if there is a DateTime
> field that is empty, the copy of the field fails for a "Invalid datetime
> format". The field isn't a NOT NULL one, so I have to accept also empty
> values... I've tried with the sequence '', "", or simply /t/t (tab is
> the field separator in those ASCII data), but the output has always been
> the same.

There's no such thing as an "empty" value of datetime.  What you can
put in is a NULL, which is not a datetime at all, but an indicator that
the field has no data in this particular table row.  NULLs work for any
data type.  The syntax for a NULL field in COPY is "\N".  (Simply
leaving the field blank, as you were trying to do, isn't good enough
since there would be no way to distinguish an empty text field from a
NULL text field.)

For example, here are a couple of rows of COPY data from a database of
my own.  There are four datetime columns in the table, and these rows
have different subsets of the four non-null (as well as nulls in several
other columns):

37    16    16    Fri Nov 13 17:00:07 1998 EST    EDF    29    S    7343    \N    s    7100    100    f    I    188
MonNov 16 07:46:38 1998 EST    \N    \N    \N    \N 
63    8    8    Thu Nov 12 16:00:10 1998 EST    EDF    49    s    11353    \N    S    12408    100    f    C    189
\N   \N    Mon Nov 16 07:46:45 1998 EST    \N    \N 

Apologies if your mailer mangles the data --- there are supposed to be
two long lines with tabs in them.

            regards, tom lane

On Sat, 21 Nov 1998, Tom Lane wrote:

> f.ermini@telemaco.it writes:
> > Hi all. I have a problem here. I have to automatize the periodical COPY
> > of a large set of ASCII data in a postgres database. The parser digests
> > everything I put in there, with an exception: if there is a DateTime
> > field that is empty, the copy of the field fails for a "Invalid datetime
> > format". The field isn't a NOT NULL one, so I have to accept also empty
> > values... I've tried with the sequence '', "", or simply /t/t (tab is
> > the field separator in those ASCII data), but the output has always been
> > the same.
>
> There's no such thing as an "empty" value of datetime.  What you can
> put in is a NULL, which is not a datetime at all, but an indicator that
> the field has no data in this particular table row.  NULLs work for any
> data type.  The syntax for a NULL field in COPY is "\N".  (Simply
> leaving the field blank, as you were trying to do, isn't good enough
> since there would be no way to distinguish an empty text field from a
> NULL text field.)
>
> For example, here are a couple of rows of COPY data from a database of
> my own.  There are four datetime columns in the table, and these rows
> have different subsets of the four non-null (as well as nulls in several
> other columns):
>
> 37    16    16    Fri Nov 13 17:00:07 1998 EST    EDF    29    S    7343    \N    s    7100    100    f    I    188
Mon Nov 16 07:46:38 1998 EST    \N    \N    \N    \N 
> 63    8    8    Thu Nov 12 16:00:10 1998 EST    EDF    49    s    11353    \N    S    12408    100    f    C    189
\N    \N    Mon Nov 16 07:46:45 1998 EST    \N    \N 
>
> Apologies if your mailer mangles the data --- there are supposed to be
> two long lines with tabs in them.
>
I think, you  could also consider using 'infinity' or '-infinity'
values for empty DATETIME fields ( choice is depending on what kind
of dates you plan to store )

Aleskey