Re: Apparent Problem With NULL in Restoring pg_dump - Mailing list pgsql-general

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