Thread: Question about numeric

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

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

On Wed, 22 May 2002, Erwin Ambrosch 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

Well '' isn't a valid numeric.  If you were expecting a NULL, the default
NULL representation is \N.  Try adding WITH NULL AS '' to your copy
command.