Postgres Pro
Downloads
Home   >   mailing lists

Thread: COPY from on date field problem

COPY from on date field problem

From
"Patrick Hatcher"
Date:
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

Re: COPY from on date field problem

From
"Patrick Hatcher"
Date:
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"
    
                    <PHatcher@macys.com>          To:     pgsql-novice@postgresql.org
    
                    Sent by:                      cc:
    
                    pgsql-novice-owner@post       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




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: COPY from on date field problem

From
Oliver Elphick
Date:
On Tue, 2002-02-26 at 22:59, Patrick Hatcher wrote:
>
> Never mind found the answer.  I forgot to do the WITH NULL as '' at the end
> of my COPY statement.

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

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "But God commendeth his love toward us, in that, while
      we were yet sinners, Christ died for us."
                                   Romans 5:8