pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Postgres DBA <postgres@nest.bistbn.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: f.ermini@telemaco.it
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Re: How do I COPY empty Datetimes?
Date: Sun, 22 Nov 1998 14:35:12 +0200 (IST)
Message-ID: <Pine.BSF.4.02.9811221427320.9779-100000@nest.bistbn.com> (raw)
In-Reply-To: <10377.911674876@sss.pgh.pa.us>



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




view thread (3+ messages)

Message-ID: <Pine.BSF.4.02.9811221427320.9779-100000@nest.bistbn.com>
Permalink:  ../Pine.BSF.4.02.9811221427320.9779-100000@nest.bistbn.com/
Also on:    postgresql.org/message-id/Pine.BSF.4.02.9811221427320.9779-100000@nest.bistbn.com

 · 

reply

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: postgres@nest.bistbn.com, tgl@sss.pgh.pa.us, f.ermini@telemaco.it, pgsql-sql@hub.org
  Subject: Re: [SQL] Re: How do I COPY empty Datetimes?
  In-Reply-To: <Pine.BSF.4.02.9811221427320.9779-100000@nest.bistbn.com>
  References: <10377.911674876@sss.pgh.pa.us> <Pine.BSF.4.02.9811221427320.9779-100000@nest.bistbn.com>

* 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