Re: How do I COPY empty Datetimes?

f(dot)ermini(at)telemaco(dot)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.

regards, tom lane