Re: Apparent Problem With NULL in Restoring pg_dump

On Thu, 15 Sep 2011, Andy Colson wrote:

> First you need to trim the \n and spaces:
>
> andy=# insert into junk values (E'GW-22');
> INSERT 0 1
> andy=# insert into junk values (E'GW-22 \n');
> INSERT 0 1
> andy=# insert into junk values (E'GW-22 \n');

Andy,

Here's what worked for me:

nevada=# \i junk.sql
CREATE TABLE
nevada=# insert into junk select * from chemistry where site_id = (E'GW-22');
INSERT 0 803
nevada=# insert into junk select * from chemistry where site_id = (E'GW-22 \n');
INSERT 0 0
nevada=# insert into junk select * from chemistry where site_id = (E'GW-22 \n');
INSERT 0 0
nevada=# insert into junk select * from chemistry where site_id = (E'GW-22\n');
INSERT 0 1409
nevada=# select '['|| rtrim(trim(trailing E'\n' from site_id)) || ']' from junk;

?column?
----------
[GW-22]
[GW-22]

and so on for 2212 rows.

> Trim it up:
>
> andy=# select '['|| rtrim(trim(trailing E'\n' from a)) || ']' from junk;

> If you have a unique index you'll wanna drop it first. Once you get that
> done, we can remove the dups.

No index on junk; I can remove it from chemistry prior to reinserting the
cleaned rows.

Also, where can I read about the select syntax you use? I find nothing
about it in Rick van der Lans' 4th edition, the most comprehensive language
reference I've read.

Thanks,

Rich