pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How do I COPY empty Datetimes?
3+ messages / 3 participants
[nested] [flat]

* How do I COPY empty Datetimes?
@ 1998-11-20 13:08 f.ermini@telemaco.it
  0 siblings, 0 replies; 3+ messages in thread

From: f.ermini@telemaco.it @ 1998-11-20 13:08 UTC (permalink / raw)
  To: pgsql-sql

To whoever is reading this mail for the second time, excuse me for the
cross-postings... I've already asked in 'general', since I've not gotten
any response I'm begging you guys in here to give me some advice...

------
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. 

I would be immensely grateful to any 1 that could shed some light...
private responses please, I've had to unsubscribe... ya know, the phone
bill!!

TIA
-----------

Fabrizio Ermini



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How do I COPY empty Datetimes?
@ 1998-11-21 19:01 Tom Lane <tgl@sss.pgh.pa.us>
  1998-11-22 12:35 ` Re: [SQL] Re: How do I COPY empty Datetimes? Postgres DBA <postgres@nest.bistbn.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 1998-11-21 19:01 UTC (permalink / raw)
  To: f.ermini@telemaco.it; +Cc: pgsql-sql@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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Re: How do I COPY empty Datetimes?
  1998-11-21 19:01 Re: How do I COPY empty Datetimes? Tom Lane <tgl@sss.pgh.pa.us>
@ 1998-11-22 12:35 ` Postgres DBA <postgres@nest.bistbn.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Postgres DBA @ 1998-11-22 12:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: f.ermini@telemaco.it; pgsql-sql@hub.org



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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1998-11-22 12:35 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-20 13:08 How do I COPY empty Datetimes? f.ermini@telemaco.it
1998-11-21 19:01 Re: How do I COPY empty Datetimes? Tom Lane <tgl@sss.pgh.pa.us>
1998-11-22 12:35 ` Postgres DBA <postgres@nest.bistbn.com>

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