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

[Jón Eiríksson]

Well, the join2 solved the data length problem and worked for me while I
was using MySQL, but I currently use SQLite as I find it less cumbersome
to set up and manage.
Regards, Jon

 >              body p { margin-bottom: 0cm; margin-top: 0pt; }            On
> 03/30/2012 07:34 PM, Jon Eiriksson wrote:            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.
>      Issue opened on trac:
>      http://trac.osgeo.org/grass/ticket/1631
>      I'm looking forward to feedback...
>
>      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.
>
>      Regards,
>      Micha
>
>
>              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=
> 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
>      This mail was received via Mail-SeCure System.
>
>
>      --  Micha Silver GIS Consultant, Arava Development Co.
> http://www.surfaces.co.il