Thread: pg_dump/psql < db.out issue

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.

Thanks in advance, Michael Davis

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

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