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

Search Discussions

  • Patrick Hatcher at Feb 26, 2002 at 10:59 pm
    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
  • Oliver Elphick at Feb 26, 2002 at 11:54 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 26, '02 at 10:46p
activeFeb 26, '02 at 11:54p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Patrick Hatcher: 2 posts Oliver Elphick: 1 post

People

Translate

site design / logo © 2022 Grokbase