Обсуждение: copy command & null datetime
The following example will cause COPY to fail on input: xxx|yyy|zzz||aaa|bbb| where the empty field in between zzz & aaa is of type datetime (don't know about other types). I know a \N will fix this, but interpreting an empty import field as NULL would certainly beef up COPY as other database systems don't output the \N which is unique to PostgreSQL. Wish list item? Ken
On Sun, 7 Mar 1999, Ken J. Wright wrote: > The following example will cause COPY to fail on input: > > xxx|yyy|zzz||aaa|bbb| > > where the empty field in between zzz & aaa is of type datetime (don't know > about other types). I know a \N will fix this, but interpreting an empty > import field as NULL would certainly beef up COPY as other database systems > don't output the \N which is unique to PostgreSQL. Wish list item? Yes, I discovered this little issue the other day. What is this \N option, though? I have some 900000+ records out of a 2.1 million row table that have NULL date fields. I ended up sticking in a dummy date (of like 1901) for those, because I knew that valid dates stopped at a particular lower cutoff date of around 1968, then ignore the earlier date altogether. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ ----------------------------------------------------------------------- A baby is God's opinion that the world should go on. -- Carl Sandburg -----BEGIN GEEK CODE BLOCK----- Version: 3.12 GAT dpu s:-- a C++++ UL++++$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++ PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y++++ ------END GEEK CODE BLOCK------
"Ken J. Wright" <ken@ori-ind.com> writes: > where the empty field in between zzz & aaa is of type datetime (don't know > about other types). I know a \N will fix this, but interpreting an empty > import field as NULL would certainly beef up COPY as other database systems > don't output the \N which is unique to PostgreSQL. You might call that "beefing up COPY" but I'd call it "breaking COPY". For data types such as text or varchar, there is a considerable difference between an empty field value and a NULL field. You propose to make it impossible to tell the difference. IMHO, COPY's primary responsibility is to serve as a lossless conduit for saving and restoring databases, not to be a user-friendly or foreign-database-compatible data import method. It *must* reliably distinguish empty strings from NULL fields. If there's some other data format you want to read, you can use a preprocessor to convert it into COPY format or INSERT statements. Now I'm certainly not particularly in love with the \N notation, and if you have a better idea I'm all ears. But failing to distinguish empty and NULL is not a better idea. regards, tom lane