COPY from on date field problem

Never mind found the answer. I forgot to do the WITH NULL as '' at the end
of my COPY statement.
Thanks

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office





"Patrick Hatcher"
<PHa[email protected]> To: [email protected]
Sent by: cc:
[email protected] Subject: [NOVICE] COPY from on date field problem
gresql.org


02/26/2002 02:46 PM






Pg ver: 7.2
I'm trying to do a COPY function from a tab delimited file into a table
defined as such:

CREATE TABLE "mdc_priceschedule" (
"keyp_price" int4 NOT NULL,
"keyf_products" int4 NOT NULL,
"date_start" date ,
"date_end" date ,
"price_normal" numeric(13, 2) ,
"inc_event_type" int2 ,
"sale_value" numeric(13, 2) ,
"price_sale" numeric(13, 2) ,
CONSTRAINT "xpkmdc_priceschedule" UNIQUE ("keyp_price")
) WITH OIDS;


record example:
112116 73440 2/9/2002 2/9/2007 49 2 0 .01
112117 73017 1/7/2002 12/7/2006 189 2 0 65.99
112118 73450 0 0 0 0
112119 73016 1/7/2002 12/6/2006 69 2 0 23.99


Some of the date fields and numeric fields maybe a NULL value. However,
whenever I try to import any record that may have a NULL value in the
date_start field for example, I get a message stating Bad date external
representation ''.
Any clue why this would happen?
Just a FYI, I have tried putting DEFAULT NULL on the date fields, but Pg
keeps removing them. I assume that this means NULL is a default.

TIA

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office

That solves this particular problem for you. However, it would cause
you problems if you also had text fields with empty strings (as opposed
to NULLs). The standard representation of null in COPY input is \N

112116 73440 2/9/2002 2/9/2007 49 2 0 .01
112117 73017 1/7/2002 12/7/2006 189 2 0 65.99
112118 73450 \N \N 0 0 0 0
112119 73016 1/7/2002 12/6/2006 69 2 0 23.99