Received: from nest.bistbn.com (nest.bistbn.com [209.88.174.52]) by hub.org (8.9.1/8.9.1) with ESMTP id HAA20691 for ; Sun, 22 Nov 1998 07:37:20 -0500 (EST) (envelope-from postgres@nest.bistbn.com) Received: from localhost (postgres@localhost) by nest.bistbn.com (8.9.1/8.9.1) with SMTP id OAA10957; Sun, 22 Nov 1998 14:35:12 +0200 (IST) Date: Sun, 22 Nov 1998 14:35:12 +0200 (IST) From: Postgres DBA To: Tom Lane cc: f.ermini@telemaco.it, pgsql-sql@hub.org Subject: Re: [SQL] Re: How do I COPY empty Datetimes? In-Reply-To: <10377.911674876@sss.pgh.pa.us> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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