[GRASS-user] after performing join all character fields are trunctated to 1

[Micha Silver]

On 03/28/2012 10:38 PM, Jennifer Boehnert wrote:
> Thank you Micha for the quick response.  When I perform this join 
> through the GUI on Windows I get the following error
>
> ERROR: value too long for type character (1)

Hi Jennifer:
Ahhh, I remember something about a bug in the v.db.join script.

The script first gets a list of columns from the join table, then loops 
thru that list, creates a new column in the target table, and runs an 
UPDATE to get all the values from the join table into the target. The 
trouble comes with columns of type CHARACTER - the script does not check 
the length of the field, so by default (in Postgresql) it creates a 
field of length 1.
Here's from the PostgreSQL manual:

The notationsvarchar(n)andchar(n)are aliases forcharacter 
varying(n)andcharacter(n), respectively.characterwithout length 
specifier is equivalent tocharacter(1). Ifcharacter varyingis used 
without length specifier, the type accepts strings of any size. The 
latter is aPostgreSQLextension.

Since the script uses the column definition "CHARACTER" and not 
"CHARACTER VARYING" the default length is 1.

Here's a way to work around the problem. Let's Suppose you have a target 
vector called 'vect', and the join table called 'otable', your text 
column is 'long_text', and the id columns for the join are 'cat' and 
'oid', then

# first drop the wrong (length 1) text column
v.db.dropcol vect col=long_text
# Now recreate that column correctly
v.db.addcol vect col="long_text varchar(255)"
# And finally the update
echo "UPDATE vect SET long_text=(SELECT long_text
     FROM otable
     WHERE otable.oid=vect.cat);"  | db.execute database=<your db> driver=pg

That's a one time fix. In order to get this working correctly 
permanently, changes are required to the v.db.join script. I'll suggest 
this to the devs.

Regards,
Micha
>
> and all my added fields which are of data type character have a length 
> of 1 instead of the original 255.  Any ideas what is going on?  I am 
> using postgreSQL 9.1 and GRASS 6.4.2 on Windows.
> Thanks Jennifer
>
>
> On 3/28/2012 12:56 PM, Micha Silver wrote:
>> On 03/28/2012 08:49 PM, Jennifer Boehnert wrote:
>>> Hello,
>>> I have performed a join between a vector map and a postgreSQL table
>>> using v.db.join.  I would like to remove this join now.  I cannot
>>> figure out how to do this.
>>
>> I think you can just drop the additional columns with v.db.dropcol.
>>
>>> Thank you Jennifer
>>>
>>> _______________________________________________
>>> grass-user mailing list
>>> grass-user at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/grass-user
>>>
>>> This mail was received via Mail-SeCure System.
>>>
>>>
>>
>>
>> -- 
>> Micha Silver
>> GIS Consultant, Arava Development Co.
>> http://www.surfaces.co.il
>>
>


-- 
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il