Re: Apparent Problem With NULL in Restoring pg_dump

On 9/15/2011 3:10 PM, Rich Shepard wrote:
> On Thu, 15 Sep 2011, Andy Colson wrote:
>
>> To restore, you are using: psql dbname < filename correct?
>
> Andy,
>
> Same error.
>
> BTW, what prompted this was my discovery that about 1400 rows with site_id
> = GW-22 had a newline appended to that string. Using emac's
> search-and-replace I took those off and new that I would probably have
> duplicate records when trying to replace the table. But, I did not expect
> these errors of extra characters after the last datum or something about
> blanks in real columns.
>
> If there's a better way for me to drop the \n versions and elimiate one of
> the resulting duplicates, please teach me how and I'll go that route.
>
> Thanks,
>
> Rich
>

It's simpler to use sql to do this.  Can you restore the table?

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');


Here are three records, with spaces and CR's.

Trim it up:

andy=# select '['|| rtrim(trim(trailing E'\n' from a)) || ']' from junk;
  ?column?
----------
  [GW-22]
  [GW-22]
  [GW-22]
(3 rows)


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

-Andy