Skip site navigation (1) Skip section navigation (2)

Re: FW: [SQL] Errors loading null dates in 7.0beta1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Colin Rothnie <colinr(at)tiwest(dot)com(dot)au>
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: FW: [SQL] Errors loading null dates in 7.0beta1
Date: 2000-03-08 16:04:37
Message-ID: 25356.952531477@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Colin Rothnie <colinr(at)tiwest(dot)com(dot)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

In response to

pgsql-sql by date

Next:From: Kyle BatemanDate: 2000-03-08 16:22:17
Subject: Re: Casts in 7.0 vs 6.5 (was Re: [SQL] 7.0beta bug (or feature)?)
Previous:From: EdDate: 2000-03-08 13:43:05
Subject: Record number...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group