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