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
[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
--
Jennifer Boehnert
GIS Coordinator
National Center of Atmospheric Research
PO Box 3000
Boulder, CO, 80307
_______________________________________________
grass-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/grass-user
