<html style="direction: ltr;">
<head>
<meta content="text/html; charset=windows-1255"
http-equiv="Content-Type">
<style>body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="preferred-charset"
bgcolor="#FFFFFF" text="#000000">
On 03/30/2012 07:34 PM, Jon Eiriksson wrote:
<blockquote cite="mid:CA32CF07-0E96-4317-9B99-5E78D1119765@hi.is"
type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<br>
Issue opened on trac:<br>
<a class="moz-txt-link-freetext" href="http://trac.osgeo.org/grass/ticket/1631">http://trac.osgeo.org/grass/ticket/1631</a><br>
I'm looking forward to feedback...<br>
<br>
The problem does not occur with sqlite because it's very lax in data
types. Sqlite ignores all the length restrictions to data types, and
somehow dynamically allocates as much space is needed for each data
field. So a column of type CHARACTER is the same as TEXT and the
same as VARCHAR(8), or VARCHAR(255) etc.<br>
<br>
Regards,<br>
Micha <br>
<br>
<br>
<blockquote cite="mid:CA32CF07-0E96-4317-9B99-5E78D1119765@hi.is"
type="cite">
<pre wrap="">
Jon
On 30.3.2012, at 09:46, Micha Silver wrote:
</pre>
<blockquote type="cite">
<pre wrap="">On 03/28/2012 10:38 PM, Jennifer Boehnert wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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)
</pre>
</blockquote>
<pre wrap="">
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
</pre>
<blockquote type="cite">
<pre wrap="">
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:
</pre>
<blockquote type="cite">
<pre wrap="">On 03/28/2012 08:49 PM, Jennifer Boehnert wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap="">
I think you can just drop the additional columns with v.db.dropcol.
</pre>
<blockquote type="cite">
<pre wrap="">Thank you Jennifer
_______________________________________________
grass-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:grass-user@lists.osgeo.org">grass-user@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/grass-user">http://lists.osgeo.org/mailman/listinfo/grass-user</a>
This mail was received via Mail-SeCure System.
</pre>
</blockquote>
<pre wrap="">
--
Micha Silver
GIS Consultant, Arava Development Co.
<a class="moz-txt-link-freetext" href="http://www.surfaces.co.il">http://www.surfaces.co.il</a>
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
--
Micha Silver
GIS Consultant, Arava Development Co.
<a class="moz-txt-link-freetext" href="http://www.surfaces.co.il">http://www.surfaces.co.il</a>
_______________________________________________
grass-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:grass-user@lists.osgeo.org">grass-user@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/grass-user">http://lists.osgeo.org/mailman/listinfo/grass-user</a>
</pre>
</blockquote>
<pre wrap="">
This mail was received via Mail-SeCure System.
</pre>
</blockquote>
<p><br>
</p>
<br>
<pre class="moz-signature" cols="72">--
Micha Silver
GIS Consultant, Arava Development Co.
<a class="moz-txt-link-freetext" href="http://www.surfaces.co.il">http://www.surfaces.co.il</a></pre>
</body>
</html>