Postgres Pro
Downloads
Home   >   mailing lists

Thread: tracking errors in psql

tracking errors in psql

From
brian
Date:
I've just gotten a dump from pgMyAdmin for a production database that i
wanted to update the dev db with. However, i'm seeing a multitude of
errors. From what i can see, the columns are mixed up in the COPY block
for a particular table. My guess is that a tab character has thrown
everything into disaray. The dump is structure & data, using COPY FROM
STDIN.

I have two questions:

1) What is the best way to track these errors? They pile up on each
other, so i can't just scroll back in the terminal (I could set the
buffer higher but i'd be scrolling all day).

2) Does anyone have any suggestions for removing the tabs in this
table's column? There's no good reason for them there (it's all HTML) so
i don't mind just removing them altogether. Do i need to use
regexp_replace(), or would replace() do the job? And are there any
quoting specifics required in order that the tab character is recognised?

Is this fine as is?

UPDATE member SET bio = replace(bio, '\t', '');

brian

Re: tracking errors in psql

From
Richard Huxton
Date:
brian wrote:
> I've just gotten a dump from pgMyAdmin for a production database that i
> wanted to update the dev db with. However, i'm seeing a multitude of
> errors. From what i can see, the columns are mixed up in the COPY block
> for a particular table. My guess is that a tab character has thrown
> everything into disaray. The dump is structure & data, using COPY FROM
> STDIN.

1. This shouldn't happen. Dump/restore should work for all data with the
standard pg_dump/pg_restore tools.

2. What is "pgMyAdmin" - I'm not sure I've heard of it.

--
   Richard Huxton
   Archonet Ltd

Re: tracking errors in psql

From
brian
Date:
Richard Huxton wrote:
> brian wrote:
>
>> I've just gotten a dump from pgMyAdmin for a production database that
>> i wanted to update the dev db with. However, i'm seeing a multitude of
>> errors. From what i can see, the columns are mixed up in the COPY
>> block for a particular table. My guess is that a tab character has
>> thrown everything into disaray. The dump is structure & data, using
>> COPY FROM STDIN.
>
>
> 1. This shouldn't happen. Dump/restore should work for all data with the
> standard pg_dump/pg_restore tools.

It did. I asked the client to send me a dump using seperate INSERTs
instead of COPY. That worked fine.

Note that the dump did not come from pg_dump, but from a third-party
software (see below).

> 2. What is "pgMyAdmin" - I'm not sure I've heard of it.
>

Right, that would be phpPGAdmin (which i've not had much experience with).

As for tracking down where the offending values were, i used \o to echo
out to a file.

brian

Re: tracking errors in psql

From
Richard Huxton
Date:
brian wrote:
>
> Right, that would be phpPGAdmin (which i've not had much experience with).
>
> As for tracking down where the offending values were, i used \o to echo
> out to a file.

If you can put together a small test case, I'm sure the phpPgAdmin team
would be interested to know.

--
   Richard Huxton
   Archonet Ltd