Обсуждение: Copy From with "null" data
-----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-----
"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
\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) >
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