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
