#1631: v.db.join and CHARACTER type columns
---------------------------+------------------------------------------------
Reporter: micha | Owner: grass-dev@…
Type: defect | Status: new
Priority: normal | Milestone: 6.4.3
Component: Shell Scripts | Version: 6.4.2
Keywords: | Platform: Unspecified
Cpu: Unspecified |
---------------------------+------------------------------------------------
The v.db.join script has a shortcoming in that it does not check the
length of datatypes when listing the columns from the 'otable' data table.
The script then creates new columns in the target vector with wrong data
types.
For example, if the 'otable' contains columns of type smallint, integer,
bigint these all become integer ("int4") in the final table. Similarly,
columns of type real and double are both created as double in the final
join. I guess this is a limitation of the postgresql db driver (?)
More serious is the case of character columns. Postgresql, by default,
creates a CHARACTER column of size 1, unless the size is explicitly stated
in the ADD COLUMN statement.
---Quote from the Postgresql Manual---[[BR]]
''
The notations varchar(n) and char(n) are aliases for character varying(n)
and character(n), respectively. character without length specifier is
equivalent to character(1). If character varying is used without length
specifier, the type accepts strings of any size. The latter is a
PostgreSQL extension.''[[BR]]
---end quote---
Since the v.db.join script (using v.db.addcol) creates the new column as
type CHARACTER, it becomes length 1 in the joined table. This is almost
always wrong.
BTW, this problem does not occur with sqlite based tables since sqlite
ignores the length of data types
---Quote from sqlite3 site----[[BR]]
''
Note that numeric arguments in parentheses that following the type name
(ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any
length restrictions (other than the large global SQLITE_MAX_LENGTH limit)
on the length of strings, BLOBs or numeric values.''[[BR]]
---end quote---
As a possible work around, here's a diff for the v.db.join script which
collects both column type and column length, and uses both to send a more
correct column definition to v.db.addcol.
{{{
[root@MS-SL6 scripts]# diff v.db.join.orig v.db.join
107a108,109
> # MS: Save column size for CHARACTER VARYING datatype
> COLSIZES=`db.describe -c driver="$driver" database="$database"
table="$GIS_OPT_OTABLE" | grep '^Column ' | cut -d':' -f4`
111c113,126
< v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col `echo
$COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`"
---
> coltype=`echo $COLTYPES | cut -d' ' -f$i`
> colsize=`echo $COLSIZES | cut -d' ' -f$i`
> case "$coltype" in
> CHARACTER)
> coldef="VARCHAR($colsize)"
> ;;
> *)
> coldef=`echo $COLTYPES | cut -d' ' -f$i | tr -s
'_' ' '`
> ;;
> esac
>
> # v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col `echo
$COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`"
> # g.message -i "Adding column $col $coldef"
> v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col $coldef"
}}}
--
Ticket URL: <http://trac.osgeo.org/grass/ticket/1631>
GRASS GIS <http://grass.osgeo.org>