Difference between revisions of "SQL"

From GRASS-Wiki
Jump to: navigation, search
(add formatting function for sqlite)
(fixing english)
Line 24: Line 24:
 
=== Formatting ===
 
=== Formatting ===
  
If your data should have values as character with zero but you have number leading zero (in the example it should be six number) in SQLite you can run  
+
If your data should have values as character with leading zero (000123) but they are lacking (123) then you can run  
 
   alter table TABLE add column NEW_COLUMN text;
 
   alter table TABLE add column NEW_COLUMN text;
 
   update TABLE set NEW_COLUMN=substr('000000'||OLD_COLUMN, length('000000'||OLD_COLUMN)-5, 6);
 
   update TABLE set NEW_COLUMN=substr('000000'||OLD_COLUMN, length('000000'||OLD_COLUMN)-5, 6);

Revision as of 06:38, 29 March 2012

SQL support in GRASS GIS

GRASS 6 uses SQL for vector attribute management.


Analysing SQL syntax errors/table import or copy problems

Sometimes it happens that db.*, v.db.* or other SQL related commands fail. For example, with

 ERROR:  syntax error at or near "Offset"
 LINE 1: ...eatureDet varchar(254), Measure double precision, Offset dou...
                                                             ^
 WARNING: Unable to create table <hydro_pointEvent>
 WARNING: Copy table failed

How to fix that? Check out for

  • column name used more than one time;
  • column name has reserved SQL word (depends on DB backend, see SQL Reserved Words Checker to quickly find out if a name is reserved)

Formatting

If your data should have values as character with leading zero (000123) but they are lacking (123) then you can run

 alter table TABLE add column NEW_COLUMN text;
 update TABLE set NEW_COLUMN=substr('000000'||OLD_COLUMN, length('000000'||OLD_COLUMN)-5, 6);