pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Errors loading null dates in 7.0beta1
4+ messages / 2 participants
[nested] [flat]

* Errors loading null dates in 7.0beta1
@ 2000-03-08 06:36 Colin Rothnie <colinr@tiwest.com.au>
  2000-03-08 08:36 ` Re: [SQL] Errors loading null dates in 7.0beta1 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Colin Rothnie @ 2000-03-08 06:36 UTC (permalink / raw)
  To: pgsql-sql

I am unable to load data into version 7.0 beta1 when the date field is null.
The same data loaded into the same table structure in 6.5.3 without errors.

Is this problem likely to have been resolved by recent changes in the
snapshot version?

Thanks in advance
Colin Rothnie



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

* Re: [SQL] Errors loading null dates in 7.0beta1
  2000-03-08 06:36 Errors loading null dates in 7.0beta1 Colin Rothnie <colinr@tiwest.com.au>
@ 2000-03-08 08:36 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2000-03-08 08:36 UTC (permalink / raw)
  To: Colin Rothnie <colinr@tiwest.com.au>; +Cc: pgsql-sql

Colin Rothnie <colinr@tiwest.com.au> writes:
> I am unable to load data into version 7.0 beta1 when the date field is null.
> The same data loaded into the same table structure in 6.5.3 without errors.

FWIW, I don't see any obvious problem:

regression=# create table foo (f1 int, f2 date);
CREATE
regression=# insert into foo values (42, null);
INSERT 686666 1
regression=# select * from foo;
 f1 | f2
----+----
 42 |
(1 row)

Can you be more specific about your problem?

			regards, tom lane



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

* FW: [SQL] Errors loading null dates in 7.0beta1
@ 2000-03-08 09:12 Colin Rothnie <colinr@tiwest.com.au>
  2000-03-08 16:04 ` Re: FW: [SQL] Errors loading null dates in 7.0beta1 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Colin Rothnie @ 2000-03-08 09:12 UTC (permalink / raw)
  To: pgsql-sql

> Tom Lane wrote:
> > FWIW, I don't see any obvious problem:
> 
> > regression=# create table foo (f1 int, f2 date);
> > CREATE
> > regression=# insert into foo values (42, null);
> > INSERT 686666 1
> > regression=# select * from foo;
> >  f1 | f2
> > ----+----
> >  42 |
> > (1 row)
> >
> >Can you be more specific about your problem?
> 
> Sorry about the ambiguity.
> I am copying data from a tab delimited text file using the psql command 
> test=> \copy collar from collar.txt;
> where the table "collar" contains a date field (not set to "NOT NULL") and
> the text file contains date fields for some records and nothing between
> two tabs for records without date data.  The same table structure and load
> file were used successfully with 6.5.3 (although I must admit that I
> haven't switched back to the old PostgreSQL to double check).
> 
> The table structure is as follows;
> DROP TABLE collar;
> CREATE TABLE collar (    
>     holeid        varchar(10) PRIMARY KEY,
>     easting       float8 NOT NULL,
>    northing       float8 NOT NULL,
>          rl       float8 NOT NULL CHECK (rl > 0),
>       depth       float8 NOT NULL,
>   basedepth       float8,
>        date       date,
>   starttime       time,
>     endtime       time,
>    location       varchar(30),
>   geologist       varchar(20),
>     driller       varchar(20),
>       rigid       varchar(10),
>    samplers       varchar(30),
>     company       varchar(10),
>     program       varchar(5),
>   loadbatch       int2
> );
> 
> Looking at it again now, I am also uncertain whether "date" is an
> allowable field name (PostrgreSQL didn't object).
> 
> Cheers
> Colin Rothnie
> 
> 
PS Sorry about the direct mail Tom, I hit the reply button without
confirming the addressee






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

* Re: FW: [SQL] Errors loading null dates in 7.0beta1
  2000-03-08 09:12 FW: [SQL] Errors loading null dates in 7.0beta1 Colin Rothnie <colinr@tiwest.com.au>
@ 2000-03-08 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2000-03-08 16:04 UTC (permalink / raw)
  To: Colin Rothnie <colinr@tiwest.com.au>; +Cc: pgsql-sql

Colin Rothnie <colinr@tiwest.com.au> writes:
> I am copying data from a tab delimited text file using the psql command 
> test=> \copy collar from collar.txt;
> where the table "collar" contains a date field (not set to "NOT NULL") and
> the text file contains date fields for some records and nothing between two
> tabs for records without date data.

The approved way to represent nulls in copy data is "\N", not just an
empty string.  This is the same for all data types.

> The same table structure and load file
> were used successfully with 6.5.3

Really?  I tried it and got

play=> \copy collar from collar.txt
ERROR:  Bad date external representation ''
PQendcopy: resetting connection
Copy failed.

which is what I'd expect.

> Looking at it again now, I am also uncertain whether "date" is an allowable
> field name (PostrgreSQL didn't object).

We allow it, but the SQL92 spec lists DATE as a <reserved word>, so it's
possible that some other DBMSes would complain.

			regards, tom lane



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


end of thread, other threads:[~2000-03-08 16:04 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-03-08 06:36 Errors loading null dates in 7.0beta1 Colin Rothnie <colinr@tiwest.com.au>
2000-03-08 08:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-03-08 09:12 FW: [SQL] Errors loading null dates in 7.0beta1 Colin Rothnie <colinr@tiwest.com.au>
2000-03-08 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>

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