varchar too long strange problem

From: Gustavo Pérez <e7640227(at)est(dot)fib(dot)upc(dot)es>
To: pgsql-admin(at)postgresql(dot)org
Subject: varchar too long strange problem
Date: 2003-09-18 20:26:48
Message-ID: 3F6A1508.5010504@est.fib.upc.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


Hi,

Looks like problems are following me . I'm having more problems
restoring a database. That database was a part of a final studies, and right
now I have to admin it.
The problem now is that when I dump a relation with (which inherits
from another) :

/pg_dump prova -C -bv -f prova.dump -U psql -Ft (and custom '-Fc'
format used too)

and then restore a certain relation, the process of restoration
vanishes with the following message :

pg_restore: [archiver (db)] could not execute query: ERROR: value
too long for type character varying(10)

Checking the tables in the catalog, I found three fields which are
defined as varchar(10), so I performed the
following query in the psql client :

SELECT length(field)
FROM table;

That shows me that no field has more than 10 characters. So while
burning my eyes in front of the screen I thoguh
about codification problems. I checked the codification of the database
by using the psql-command : \l, which shows
that the original database is using SQL_ASCII. So I tryed to create a
brand new database like :

/usr/bin/createdb -U postgres -T template0 -E SQL_ASCII prova1

and then tryed to restore it :

pg_restore -v -Ft -U psql -d prova1 prova.dump

Could it be a locale problem ? I don't think so, just because I'm
using tar/custom format. Moreover
I'm restoring the DB in the same machine where I created it, so these
reasons make think that no non-unix
characters may be added.
I'm thinking of copying the entire table to a new with :

SELECT * into new_table
FROM old_table;

and then creating a view called old_table (in order to keep working
all the applications). But this is (IMHO)
not a nice solution, just because I'd like to know the reason of the
problem.

May anyone help me with this ? I'll be very gratefully.

Thanks in advance,

Gustavo Pérez

Browse pgsql-admin by date

  From Date Subject
Next Message Ravi T Ramachandra 2003-09-19 04:24:39 Re: Postgresql UPDATE LOCKS unrelated rows.
Previous Message Josh Berkus 2003-09-18 18:02:10 Re: How to force an Index ?