COPY assumes that an empty field represents an empty
string; being interpreted as a string, it will not be
accepted into number or date type fields.
If you are happy for those fields to be recognized as
NULL, then all you need to do is add
"with NULL as ''" to the end of your COPY command.
Alternatively, you will need to edit your text file to
insert dummy values into those fields.
--- Erwin Ambrosch <ambre@ebutec.com> wrote:
> Hi,
>
> when I import data from a text file (fildes are
> seperated with pipes) I get
> an error for fields of type numeric if there are no
> data for this fields.
>
> Example:
>
> CREATE TABLE mytable (
> id SERIAL,
> num VARCHAR(32),
> cost NUMERIC(6,2),
> info TEXT
> );
>
> A line to import from a text file.
>
> 1|123abc||This part is....
>
> Because of the empty field for the column cost, I
> get the following error:
>
> ERROR: copy: line 1, Bad numeric input format ''
> PQendcopy: resetting connection
>
> This works (zero for the column cost):
> 1|123abc|0|This part is....
>
> Thanks Erwin
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com