Re: Apparent Problem With NULL in Restoring pg_dump [SOLVED]

On Fri, 16 Sep 2011, Rich Shepard wrote:

> Scrolling through the table with rows ordered by date and chemical I find
> no duplicates ... so far. However, what I do find is that the above did not
> work:

Turns out there was 1 duplicate. Reading the psql man page and making an
error in the \copy command syntax taught me the (E' ') escape syntax. Kool!

To close this thread, here's what I did to remove the table rows with
embedded newlines:

1) Made a copy of the chemistry table named 'junk'.
2) insert into junk select * from chemistry where site_id = 'GW-22';
3) insert into junk select * from chemistry where site_id = (E'GW-22\n');
4) \copy from junk to '</full/path/to/foo.sql>'
5) In emacs, use global search and replace to remove unwanted '\n'.
6) delete from junk where site_id = 'GW-22';
7) delete from junk where site_id = (E'GW-22\n');
8) \copy junk from '<full/path/to/foo.sql'>
9) delete from chemistry where site_id = 'GW-22';
10) delete from chemistry where site_id = (E'GW-22\n');
11) insert into chemistry select * from junk;

The latter found the one duplicate so I fixed that in emacs, then dropped
junk and repeated steps 8-11.

This message is as much for my future reference as it is for others who
might face the same problem.

Rich