Postgres Pro
Downloads
Home   >   mailing lists

Thread: Copy From with "null" data

Copy From with "null" data

From
"James Moe"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,
  I am attempting to import data from a text file (tab-delimited) that has NULL fields
of the form "(NULL)". (The source dbms does not have DUMP command.) According to the
v7.3 docs (http://www.postgresql.org/docs/7.3/static/sql-copy.html):

null string -- The string that represents a NULL value. The default is "\N"
(backslash-N). [...] On a copy in, any data item that matches this string will be
stored as a NULL value, [...].

  If I read that correctly, when the input parser encounters the NULL string (say,
"\N"), it gives the corresponding field a NULL value. That is not happening. Instead
this bizarre error message is produced in psql:

": can't parse "ne 1, pg_atoi: error in "1

  The quotes are exactly as shown. It looks like the first part of the line is being
overwritten.
  I have tried replacing the NULL string with \N, '', 'NULL'. And have tried using
"with null as '(NULL)'" along with other variants.


  A description of the table:

    Column     |         Type          |         Modifiers
- ---------------+-----------------------+---------------------------
 prod_no       | character(16)         | not null default 'XXX666'
 description   | character varying(80) | default ''
 price_each    | numeric(15,2)         | default 0.00
 special_price | numeric(15,2)         | default 0.00
 special_start | date                  | default '9999-12-31'
 special_end   | date                  | default '9999-12-31'
 units         | character(8)          | default 'Ea'
 weight        | numeric(6,2)          | default 0
 num_per_box   | integer               | default 1
 wt_per_box    | numeric(6,2)          | default 1
 is_archived   | smallint              | default 0
Indexes: vprd_dat_pkey primary key btree (prod_no)


  A typical line of input data (wrapped, no doubt):

'A111'    'Is Your Net Working? Audio'    34.95    (NULL)    (NULL)    (NULL)    'Ea'    (NULL)
    (NULL)    (NULL)    1




- --
jimoe at sohnen-moe dot com
pgp/gpg public key: http://www.keyserver.net/en/
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 5.0 OS/2 for non-commercial use
Comment: PGP 5.0 for OS/2
Charset: cp850

wj8DBQE/PelbsxxMki0foKoRAoXHAJ4pPHGyYViziTFxn98jOBb2dKwmoACg7XOH
jvi8HtsC1p0nbKyb6R9fsS4=
=OZ5i
-----END PGP SIGNATURE-----

Re: Copy From with "null" data

From
Tom Lane
Date:
"James Moe" <jimoe@sohnen-moe.com> writes:
> this bizarre error message is produced in psql:

> ": can't parse "ne 1, pg_atoi: error in "1

That's unrelated to your NULL issue.  It looks like you have
Windows-style newlines (\r\n) in your data.  COPY only likes
Unix-style newlines (\n).  It thinks the \r is a data character.

I think this is finally relaxed for 7.4, but in all current releases
you need to get rid of the carriage return characters.

            regards, tom lane

Re: Copy From with "null" data

From
Dennis Gearon
Date:
\n  or \N is a line terminator/record separator, right?

James Moe wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>   I am attempting to import data from a text file (tab-delimited) that has NULL fields
> of the form "(NULL)". (The source dbms does not have DUMP command.) According to the
> v7.3 docs (http://www.postgresql.org/docs/7.3/static/sql-copy.html):
>
> null string -- The string that represents a NULL value. The default is "\N"
> (backslash-N). [...] On a copy in, any data item that matches this string will be
> stored as a NULL value, [...].
>
>   If I read that correctly, when the input parser encounters the NULL string (say,
> "\N"), it gives the corresponding field a NULL value. That is not happening. Instead
> this bizarre error message is produced in psql:
>
> ": can't parse "ne 1, pg_atoi: error in "1
>
>   The quotes are exactly as shown. It looks like the first part of the line is being
> overwritten.
>   I have tried replacing the NULL string with \N, '', 'NULL'. And have tried using
> "with null as '(NULL)'" along with other variants.
>
>
>   A description of the table:
>
>     Column     |         Type          |         Modifiers
> - ---------------+-----------------------+---------------------------
>  prod_no       | character(16)         | not null default 'XXX666'
>  description   | character varying(80) | default ''
>  price_each    | numeric(15,2)         | default 0.00
>  special_price | numeric(15,2)         | default 0.00
>  special_start | date                  | default '9999-12-31'
>  special_end   | date                  | default '9999-12-31'
>  units         | character(8)          | default 'Ea'
>  weight        | numeric(6,2)          | default 0
>  num_per_box   | integer               | default 1
>  wt_per_box    | numeric(6,2)          | default 1
>  is_archived   | smallint              | default 0
> Indexes: vprd_dat_pkey primary key btree (prod_no)
>
>
>   A typical line of input data (wrapped, no doubt):
>
> 'A111'    'Is Your Net Working? Audio'    34.95    (NULL)    (NULL)    (NULL)    'Ea'    (NULL)
>     (NULL)    (NULL)    1
>
>
>
>
> - --
> jimoe at sohnen-moe dot com
> pgp/gpg public key: http://www.keyserver.net/en/
> -----BEGIN PGP SIGNATURE-----
> Version: PGPfreeware 5.0 OS/2 for non-commercial use
> Comment: PGP 5.0 for OS/2
> Charset: cp850
>
> wj8DBQE/PelbsxxMki0foKoRAoXHAJ4pPHGyYViziTFxn98jOBb2dKwmoACg7XOH
> jvi8HtsC1p0nbKyb6R9fsS4=
> =OZ5i
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: Copy From with "null" data

From
"Andrew L. Gould"
Date:
On Sunday 17 August 2003 08:53 pm, Dennis Gearon wrote:
> \n  or \N is a line terminator/record separator, right?
>
\n is a line terminator (newline); but I think it is case-sensitive.  If it
weren't \N could not be used to represent NULL.

Andrew Gould