From: Tom Lane <tgl@sss.pgh.pa.us>
To: f.ermini@telemaco.it
Cc: pgsql-sql@hub.org
Subject: Re: How do I COPY empty Datetimes?
Date: Sat, 21 Nov 1998 14:01:16 -0500
Message-ID: <10377.911674876@sss.pgh.pa.us> (raw)
In-Reply-To: <199811210400.XAA12759@hub.org>
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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, f.ermini@telemaco.it, pgsql-sql@hub.org
Subject: Re: How do I COPY empty Datetimes?
In-Reply-To: <10377.911674876@sss.pgh.pa.us>
References: <199811210400.XAA12759@hub.org> <10377.911674876@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox