<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML-esimuotoiltu Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.Shkpostityyli17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.HTML-esimuotoiltuChar
{mso-style-name:"HTML-esimuotoiltu Char";
mso-style-priority:99;
mso-style-link:HTML-esimuotoiltu;
font-family:"Courier New";
mso-fareast-language:FI;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FI" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Andrea wrote:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<pre>>Hi Jukka,<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre><o:p> </o:p></pre>
<pre>>Rahkonen Jukka (MML) wrote<o:p></o:p></pre>
<pre><span lang="EN-US">>><i> Do you know some database that accepts SQL "ALTER TABLE foo ADD COLUMN<o:p></o:p></i></span></pre>
<pre><span lang="EN-US">>><i> bar1, bar2"? At least SQLite and PostgreSQL don't. That kind of usage is<o:p></o:p></i></span></pre>
<pre><span lang="EN-US">>><i> not mentioned in a bunch of documents about SQL language which I browsed<o:p></o:p></i></span></pre>
<pre><span lang="EN-US">>><i> through, for example </i></span><i><a href="http://www.w3schools.com/sql/sql_alter.asp."><span lang="EN-US">http://www.w3schools.com/sql/sql_alter.asp.</span></a></i><i><span lang="EN-US"><o:p></o:p></span></i></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">>it's certainly a PostgreSQL syntax, and my (wrong) question derives from it.<o:p></o:p></span></pre>
<pre><span lang="EN-US">>And it's just like you wrote:<o:p></o:p></span></pre>
<pre><span lang="EN-US">>ALTER TABLE public.table ADD COLUMN col1 int, ADD COLUMN col2 int;<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">>And in MySQL should be something like:<o:p></o:p></span></pre>
<pre><span lang="EN-US">>ALTER TABLE table<o:p></o:p></span></pre>
<pre><span lang="EN-US">>ADD COLUMN colA VARCHAR(10) NOT NULL,<o:p></o:p></span></pre>
<pre><span lang="EN-US">>ADD COLUMN colB INT(10) UNSIGNED NOT NULL,<o:p></o:p></span></pre>
<pre><span lang="EN-US">>AFTER thiscolumn<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">>I think it works also in Microsoft SQL server and Oracle.<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">Hi,<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">Using the same SQL syntax that works with pgAdmin works also with ogrinfo:<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">ogrinfo PG:"dbname='mydb' host='localhost' port='5432' user='myself'" -sql "alter table foo add column bar7 text, add column bar8 text"<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">The syntax with PostgreSQL seems really require two ADD COLUMNs<o:p></o:p></span></pre>
<pre><span lang="EN-US">ALTER TABLE foo ADD COLUMN bar4 text, ADD COLUMNS bar5 text;<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">In Oracle this is supported with syntax<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">alter table<br> table_name<br>add<br> (<br> column1_name column1_datatype column1_constraint, <br> column2_name column2_datatype column2_constraint,<br> column3_name column3_datatype column3_constraint<br> );<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">That syntax is not accepted by PostgreSQL, tested with<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">alter table foo add column (bar3 text, bar4 text);<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">ERROR: syntax error at or near "("<o:p></o:p></span></pre>
<pre><span lang="EN-US">LINE 1: alter table foo add column (bar3 text, bar4 text)<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">SQLite accepts only one column or perhaps I did not find the right syntax.<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">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.<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US">-Jukka Rahkonen-<o:p></o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre><span lang="EN-US"><o:p> </o:p></span></pre>
<pre>>Best regards,<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Andrea<o:p></o:p></pre>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>