pg_dump/psql < db.out issue

On Tue, 02 Jan 2001 04:46:27 Michael Davis wrote:

I have several tables with text fields that contain single quotes (').
Pg_dump exports these tables and the single quotes (') okay. Psql,
however, will not import the data into the tables because of the single
quote (') in one of the columns in one of the records. Any idea how to
work around this other than using the -d/-D option in pg_dump? I like the
-d option in pg_dump, however, importing the data via pgsql is much slower
with this option.

I used the -D option and pgsql had no problem re-importing data that
contained single quotes. It seemed to me that it should have failed because
the quotes were not escaped, but it worked anyway.

You could try copying the data out to a file, dumping just the schema, then
doing the reverse to rebuild the database. The data would be tab-delimited
and the quotes would not be an issue.

Tony
--
Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/&gt;
PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26 C484 A42A 60DD 6C94 239D
Chat: AOL/Yahoo: TonyG05 ICQ: 91183266
Linux. The choice of a GNU Generation. <http://www.linux.org/&gt;

Michael Davis <mdavis@sevainc.com> writes:

I have several tables with text fields that contain single quotes (').
Pg_dump exports these tables and the single quotes (') okay. Psql,
however, will not import the data into the tables because of the single
quote (') in one of the columns in one of the records.

Huh? That's worked just fine for a long time. What version are you
running?

play=> select * from foo;
f1
----------
I'm here
(1 row)

play=> \q
$ pg_dump -t foo play >quote.sql
$ cat quote.sql
\connect - tgl
CREATE TABLE "foo" (
"f1" text
);
COPY "foo" FROM stdin;
I'm here
\.
$ psql play
Welcome ...
play=> drop table foo;
DROP
play=> \i quote.sql
You are now connected as new user tgl.
CREATE
play=> select * from foo;
f1
----------
I'm here
(1 row)

play=>

regards, tom lane

Thanks for pointing this out. I tested this again and it works great. I
have been trying to make sure I understand how to recreate a database from
the dump files. The error I was seeing must have been caused by something
else. Sorry for the trouble and thanks for the response.

-----Original Message-----
From: Tom Lane [SMTP:tgl@sss.pgh.pa.us]
Sent: Tuesday, January 02, 2001 9:45 AM
To: mdavis@sevainc.com
Cc: 'pgsql-admin@postgresql.org'; 'pgsql-general@postgresql.org'
Subject: Re: pg_dump/psql < db.out issue

Michael Davis <mdavis@sevainc.com> writes:

I have several tables with text fields that contain single quotes (').
Pg_dump exports these tables and the single quotes (') okay. Psql,
however, will not import the data into the tables because of the single
quote (') in one of the columns in one of the records.

Huh? That's worked just fine for a long time. What version are you
running?

play=> select * from foo;
f1
----------
I'm here
(1 row)

play=> \q
$ pg_dump -t foo play >quote.sql
$ cat quote.sql
\connect - tgl
CREATE TABLE "foo" (
"f1" text
);
COPY "foo" FROM stdin;
I'm here
\.
$ psql play
Welcome ...
play=> drop table foo;
DROP
play=> \i quote.sql
You are now connected as new user tgl.
CREATE
play=> select * from foo;
f1
----------
I'm here
(1 row)

play=>

regards, tom lane