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