Re: reloading really big tables

On 8/4/02 3:11 PM, "Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)com> wrote:

> Howdy:
>
> Moving data from PostgreSQL 7.1.3 to PostgreSQL 7.2.1
> and am trying to reload a few tables (large ones).
>
> So, to save my original tables, I used
> the new pg_dump (7.2.1) and have them as flat files.
> When I try to restore some of the files, I get:
>
> [error]
>
> psql:table_detail.Fri:48:
> ERROR: copy: line 1, value too long for type character(1)
>
> psql:table_detail.Fri:48:
> lost synchronization with server, resetting connection
>
> [/error]
>
> What does this mean? Why isn't the table recreated?
> Is it too big? It seems that smaller tables are okay
> when I restore them.
>
> I've also tried the " cat file| psql -U postgres -d database "
> and still get the same error.

Hi Shaunn:

The problem has to do with the fact that 7.2 will now reject strings that
are too long for a CHAR column. If you have defined the column in question
simply as CHAR, that is implicitly CHAR(1) (as the error message says), and
anything longer than a single character will now be rejected as too long.

To fix the problem, you may need to figure out the maximum length of the
strings in that column, and redefine the table schema so that that column is
CHAR(n) where n is the maximum length of the strings in the original column.
(You don't have to count trailing spaces if you don't want to, they should
be silently truncated). But it should be simple enough to edit the schema in
the pg_dump output prior to loading it, once you decide on a suitable value
for n.

-- sgl

=======================================================
Steve Lane

Vice President
Chris Moyer Consulting, Inc.
833 West Chicago Ave Suite 203

Voice: (312) 433-2421 Email: slane(at)fmpro(dot)com
Fax: (312) 850-3930 Web: http://www.fmpro.com
=======================================================