Copy From with "null" data

From: "James Moe" <jimoe(at)sohnen-moe(dot)com>
To: "Postgresql General Mail List" <pgsql-general(at)postgresql(dot)org>
Subject: Copy From with "null" data
Date: 2003-08-16 08:20:43
Message-ID: auto-000000542866@sohnen-moe.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message The Hermit Hacker 2003-08-16 08:29:19 Re: Why the duplicate messages to pgsql-general?
Previous Message Shridhar Daithankar 2003-08-16 08:17:22 Re: Why the duplicate messages to pgsql-general?