Thread: Dump produces file with new line characters
This is probably a newbie question but here goes: We are using Postgres 7.3 on Red Hat 9. We are doing dumps with a script that does: pg_dump databasename > outfile 2>> logfile The resulting dump files occasionally have a \N at the end of a line of data following the copy command. This causes aproblem when I try to load them back in bacause psql thinks the \N (a new line character?) is a psql command. So, how doI avoid the \N in the dump and/or ignore it in the reload? Any help would be much appreciated. Mark Spruill 770.448.3900 mark.spruill@mightyautoparts.com
"Mark Spruill" <mark.spruill@mightyautoparts.com> writes:
> We are using Postgres 7.3 on Red Hat 9. We are doing dumps with a script that does:
> pg_dump databasename > outfile 2>> logfile
> The resulting dump files occasionally have a \N at the end of a line of data following the copy command. This
causesa problem when I try to load them back in bacause psql thinks the \N (a new line character?) is a psql command.
So,how do I avoid the \N in the dump and/or ignore it in the reload?
This is a new one on me. Can you show a specific example?
Also, 7.3.what exactly?
regards, tom lane
Tom,
Thanks for the reply. psql --version reports 7.3.2.
Following is an excerpt from the dump file.
COPY compmstr (alienvendorcode, alienlinecode, alienpartnumber, jobber, list, da
te, replacedbypart, specialmarkets, discount) FROM stdin;
001 66 16-4002 32.50 65.65 2003-05-26 00:00:00
\N 0.00 0.00
001 66 16-4003 32.50 65.65 2003-05-26 00:00:00
\N 0.00 0.00
001 66 16-4006 32.50 65.65 2003-05-26 00:00:00
\N 0.00 0.00
001 66 16-4013 32.50 65.65 2003-05-26 00:00:00
\N 0.00 0.00
See what I mean? I am wondering if there is actually a new line character in the database or if the \N is representing
aNULL value?
FYI, this statement in the live database:
select * from compmstr where alienvendorcode = '001' and alienpartnumber = '16-4002';
Provided this results exported from pgadmin with quotes:
"001","66","16-4002 ","32.50","65.65","2003-05-26 00:00:00","","0.00","0.00"
The field in question appears empty, but at this point it has been selected, exported, opened in Notepad.
Regards,
Mark Spruill
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, June 23, 2004 4:26 PM
To: Mark Spruill
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Dump produces file with new line characters
"Mark Spruill" <mark.spruill@mightyautoparts.com> writes:
> We are using Postgres 7.3 on Red Hat 9. We are doing dumps with a script that does:
> pg_dump databasename > outfile 2>> logfile
> The resulting dump files occasionally have a \N at the end of a line of data following the copy command. This
causesa problem when I try to load them back in bacause psql thinks the \N (a new line character?) is a psql command.
So,how do I avoid the \N in the dump and/or ignore it in the reload?
This is a new one on me. Can you show a specific example?
Also, 7.3.what exactly?
regards, tom lane
"Mark Spruill" <mark.spruill@mightyautoparts.com> writes:
> Following is an excerpt from the dump file.
> COPY compmstr (alienvendorcode, alienlinecode, alienpartnumber, jobber, list, da
> te, replacedbypart, specialmarkets, discount) FROM stdin;
> 001 66 16-4002 32.50 65.65 2003-05-26 00:00:00
> \N 0.00 0.00
> 001 66 16-4003 32.50 65.65 2003-05-26 00:00:00
> \N 0.00 0.00
> 001 66 16-4006 32.50 65.65 2003-05-26 00:00:00
> \N 0.00 0.00
> 001 66 16-4013 32.50 65.65 2003-05-26 00:00:00
> \N 0.00 0.00
> See what I mean? I am wondering if there is actually a new line character in the database or if the \N is
representinga NULL value?
The \N's represent nulls, yes. The question is why psql has a problem
with them. It should know that it's inside COPY data and not
misinterpret them as psql commands. This is not something that's
changed lately --- we've always handled NULLs like this --- so I'm
mystified why you are having a problem. Can you characterize the cases
in which psql gets confused?
regards, tom lane
Tom,
I have solved the problem. I don't know exactly what I did, but I think it had something to do with a screwed up
"template1". When I first tried to load the database, I connected to template1 instead of the database I wanted to
load. Things went down hill from there. So I finally just re-initialized the whole database cluster and now it seems
tobe working OK. I guess this one was just stupid user error, but I learned a lesson. Don't mess with template1!
Thanks for your help,
Mark
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, June 23, 2004 5:53 PM
To: Mark Spruill
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Dump produces file with new line characters
"Mark Spruill" <mark.spruill@mightyautoparts.com> writes:
> Following is an excerpt from the dump file.
> COPY compmstr (alienvendorcode, alienlinecode, alienpartnumber, jobber, list, da
> te, replacedbypart, specialmarkets, discount) FROM stdin;
> 001 66 16-4002 32.50 65.65 2003-05-26 00:00:00
> \N 0.00 0.00
> 001 66 16-4003 32.50 65.65 2003-05-26 00:00:00
> \N 0.00 0.00
> 001 66 16-4006 32.50 65.65 2003-05-26 00:00:00
> \N 0.00 0.00
> 001 66 16-4013 32.50 65.65 2003-05-26 00:00:00
> \N 0.00 0.00
> See what I mean? I am wondering if there is actually a new line character in the database or if the \N is
representinga NULL value?
The \N's represent nulls, yes. The question is why psql has a problem
with them. It should know that it's inside COPY data and not
misinterpret them as psql commands. This is not something that's
changed lately --- we've always handled NULLs like this --- so I'm
mystified why you are having a problem. Can you characterize the cases
in which psql gets confused?
regards, tom lane