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

[Jennifer Boehnert]

Thank you very much for the workaround.  I will give that a try.
Jennifer


On 3/30/2012 1:46 AM, 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
>>>
>>
>
>

-- 
Jennifer Boehnert
GIS Coordinator
National Center of Atmospheric Research
PO Box 3000
Boulder, CO, 80307