Copy From with "null" data

Started by James Moeover 22 years ago4 messagesgeneral
Jump to latest
#1James Moe
jimoe@sohnen-moe.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: James Moe (#1)
Re: Copy From with "null" data

"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

#3Dennis Gearon
gearond@cvc.net
In reply to: James Moe (#1)
Re: Copy From with "null" data

\n or \N is a line terminator/record separator, right?

James Moe wrote:

Show quoted text

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

#4Andrew L. Gould
algould@datawok.com
In reply to: Dennis Gearon (#3)
Re: Copy From with "null" data

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