[gdal-dev] How to add more than one column to a shapefile using ogrinfo

[Rahkonen Jukka (MML)]

Andrea wrote:


>Hi Jukka,





>Rahkonen Jukka (MML) wrote

>> Do you know some database that accepts SQL "ALTER TABLE foo ADD COLUMN

>> bar1, bar2"? At least SQLite and PostgreSQL don't. That kind of usage is

>> not mentioned in a bunch of documents about SQL language which I browsed

>> through, for example http://www.w3schools.com/sql/sql_alter.asp.



>it's certainly a PostgreSQL syntax, and my (wrong) question derives from it.

>And it's just like you wrote:

>ALTER TABLE public.table ADD COLUMN col1 int, ADD COLUMN col2 int;



>And in MySQL should be something like:

>ALTER TABLE table

>ADD COLUMN colA VARCHAR(10) NOT NULL,

>ADD COLUMN colB INT(10) UNSIGNED NOT NULL,

>AFTER thiscolumn



>I think it works also in Microsoft SQL server and Oracle.



Hi,



Using the same SQL syntax that works with pgAdmin works also with ogrinfo:



ogrinfo PG:"dbname='mydb' host='localhost' port='5432' user='myself'" -sql "alter table foo add column bar7 text, add column bar8 text"



The syntax with PostgreSQL seems really require two ADD COLUMNs

ALTER TABLE foo ADD COLUMN bar4 text, ADD COLUMNS bar5 text;



In Oracle this is supported with syntax



alter table
   table_name
add
   (
   column1_name column1_datatype column1_constraint,
   column2_name column2_datatype column2_constraint,
   column3_name column3_datatype column3_constraint
   );



That syntax is not accepted by PostgreSQL, tested with



alter table foo add column (bar3 text, bar4 text);



ERROR:  syntax error at or near "("

LINE 1: alter table foo add column (bar3 text, bar4 text)



SQLite accepts only one column or perhaps I did not find the right syntax.



It seems to me that adding a general support for adding multiple columns with a single request into OGR SQL dialect would require to go through every driver: With PostgreSQL, build SQL statement with two ADD COLUMNs, with Oracle use one ADD and list of column definition, with SQLite make as many separate ALTER TABLEs as needed, with file based formats do whatever is needed. That is for sure doable but feels like a big work. Users should also be somehow aware that certain drivers use by default some other SQL dialect than the OGR SQL and adding multiple columns would require different syntax with native dialect and with OGR SQL.



-Jukka Rahkonen-









>Best regards,



Andrea

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20160502/8b06b1a5/attachment-0001.html>