Re: Copy From with "null" data

\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(at)postgresql(dot)org)
>