Thread: psql command line question..
I have a bunch of huge inserts in a flat file. They are in proper sql context and I can take each individual and copy/pasteit into psql and it inserts fine. I tried doing a psql -e dbname < file.sql and I get errors. Is there anythingspecific I need to do to the file format? Each query is on its own new line. I didn't know if I should add a semicolonto the end so I did just to check it.. but it doesn't seem to make a difference. I tried doing this through DBD::Pgbut it seems to complain about an extra apostrophe that doesn't exist (which psql did until I quite quoting my numerics). I noticed in Bruces readme that it said that psql would do this sometimes if you have to many apostrophes.. doesthis also affect DBD::Pg? Travis
> Date: Tue, 28 Jan 2003 23:53:49 -0500
> From: "Williams, Travis L, NPONS" <tlw@att.com>
>
> I have a bunch of huge inserts in a flat file.
> They are in proper sql context and I can take each individual and copy/paste
> it into psql and it inserts fine. I tried doing a psql -e dbname < file.sql
> and I get errors. Is there anything specific I need to do to the file format?
>
I have observed very strange behaviour with psql when the input data contains
TAB characters. Maybe that's your problem too.
Replacing nonprintable characters with blanks is somewhat tricky, but possible
with sed:
# 0B is the hex code for TAB
char2replace="`echo -e \x0B`"
sed "s/$char2replace/ /g" ...
Hope this helps,
Christoph Dalitz
Well, I am always amazed by the detour people will take... Try: tr '\011' ' ' Use tr for all character based translation. By the way the TAB character is CTRL-I or x09 or 011 (octal) not x0B. Christoph Dalitz wrote: > > > Date: Tue, 28 Jan 2003 23:53:49 -0500 > > From: "Williams, Travis L, NPONS" <tlw@att.com> > > > > I have a bunch of huge inserts in a flat file. > > They are in proper sql context and I can take each individual and copy/paste > > it into psql and it inserts fine. I tried doing a psql -e dbname < file.sql > > and I get errors. Is there anything specific I need to do to the file format? > > > I have observed very strange behaviour with psql when the input data contains > TAB characters. Maybe that's your problem too. > > Replacing nonprintable characters with blanks is somewhat tricky, but possible > with sed: > > # 0B is the hex code for TAB > char2replace="`echo -e \x0B`" > sed "s/$char2replace/ /g" ... > > Hope this helps, > > Christoph Dalitz > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
On Tue, 2003-01-28 at 23:53, Williams, Travis L, NPONS wrote: > I have a bunch of huge inserts in a flat file. They are in proper sql > context and I can take each individual and copy/paste it into psql and > it inserts fine. I tried doing a psql -e dbname < file.sql and I get > errors. Which errors, exactly? Also, can you give us a sample of the file's contents? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
I found the problem.. I was searching through the archive because what I am doing is getting a bcp dump from a SQL server. It was full of nulls and that was the problem.. I noticed if I VI'd the file then resaved it everything was okay.. so basically I added the -k option for bcp and am now doing a tr '\000' ' ' (well basically that.. as I'm doing it in perl).. and everything is OK.. Thanks for the help though.. Travis -----Original Message----- From: Jean-Luc Lachance [mailto:jllachan@nsd.ca] Sent: Wednesday, January 29, 2003 11:01 AM To: Christoph Dalitz Cc: Williams, Travis L, NPONS; pgsql-general@postgresql.org Subject: Re: [GENERAL] psql command line question.. Well, I am always amazed by the detour people will take... Try: tr '\011' ' ' Use tr for all character based translation. By the way the TAB character is CTRL-I or x09 or 011 (octal) not x0B. Christoph Dalitz wrote: > > > Date: Tue, 28 Jan 2003 23:53:49 -0500 > > From: "Williams, Travis L, NPONS" <tlw@att.com> > > > > I have a bunch of huge inserts in a flat file. > > They are in proper sql context and I can take each individual and copy/paste > > it into psql and it inserts fine. I tried doing a psql -e dbname < file.sql > > and I get errors. Is there anything specific I need to do to the file format? > > > I have observed very strange behaviour with psql when the input data contains > TAB characters. Maybe that's your problem too. > > Replacing nonprintable characters with blanks is somewhat tricky, but possible > with sed: > > # 0B is the hex code for TAB > char2replace="`echo -e \x0B`" > sed "s/$char2replace/ /g" ... > > Hope this helps, > > Christoph Dalitz > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html