From: | Postgres DBA <postgres(at)nest(dot)bistbn(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | f(dot)ermini(at)telemaco(dot)it, pgsql-sql(at)hub(dot)org |
Subject: | Re: [SQL] Re: How do I COPY empty Datetimes? |
Date: | 1998-11-22 12:35:12 |
Message-ID: | Pine.BSF.4.02.9811221427320.9779-100000@nest.bistbn.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sat, 21 Nov 1998, Tom Lane wrote:
> 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.
>
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
From | Date | Subject | |
---|---|---|---|
Next Message | sqyang | 1998-11-23 02:41:22 | optimization |
Previous Message | Herouth Maoz | 1998-11-22 11:18:03 | Re: [SQL] optimizing 2-table join w/millions of rows |