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

[Jon Eiriksson]

I recall a similar problem with MySQL, and that Micha Silver produced a join2 script that did not truncate (maybe it can be found in the mail archives?), - this improved script has, apparently, not made it into the most recent releases. SQLite manages with the current join, at least on my Mac.

Jon

On 30.3.2012, at 09:46, Micha Silver wrote:

> 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
> 
> _______________________________________________
> grass-user mailing list
> grass-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/grass-user