Re: Copy From with "null" data - Mailing list pgsql-general

\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)
>