<div dir="ltr">Afaik as I know, slightly more involved sql on a postgresql table requires dialect=sqlite to work at all, e.g. (picked a random example using ogr2ogr instead of ogrinfo)<div>ogr2ogr -f postgresql -dialect sqlite -append PG:"user=%user% dbname=%dbname%" -sql "select trim(coalesce(iv1,''),';') as fidstring, code, st_union(geometry) as multipolygon from alltogether_pruned_out group by iv1,code" -nln %schema%.%baseout%_pruned -nlt multipolygon alltogether_pruned_out.shp<br></div><div>This gives me acceptable performance, btw. It's handling a really large dataset, so having to wait for some minutes did not bother me.</div><div>This is on version 3.4.2.</div><div>The transaction behavior in ogr2ogr can be influenced with the -gt option, with -gt unlimited to do everything in a single transaction.</div><div>But it would be nice if this imo strange need to refer to dialect sqlite were removed. Especially strange when you use postgis functions in the sql statement...</div><div>Best regards,</div><div>Jan</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jun 9, 2022 at 9:46 AM Rahkonen Jukka <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="FI" style="overflow-wrap: break-word;">
<div class="gmail-m_-2181917563230982579WordSection1">
<p class="MsoNormal"><span>Hi,<u></u><u></u></span></p>
<p class="MsoNormal"><span><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Do not use “-dialect sqlite” if you play with PostgreSQL but let GDAL to use the native PG SQL dialect.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<div>
<div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b> gdal-dev <<a href="mailto:gdal-dev-bounces@lists.osgeo.org" target="_blank">gdal-dev-bounces@lists.osgeo.org</a>>
<b>Puolesta </b>Andreas Oxenstierna<br>
<b>Lähetetty:</b> torstai 9. kesäkuuta 2022 9.50<br>
<b>Vastaanottaja:</b> <a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> [gdal-dev] ogrinfo UPDATE performance request<u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div name="messageBodySection">
<div>
<p class="MsoNormal">Dear developers<br>
<br>
Ogr SQL update capabilities are really impressing but there is one major performance issue with update of many features, exemplified by:<br>
ogrinfo -dialect sqlite -sql "UPDATE <table> SET x = 1" PG:”<connection>"<br>
<br>
This is painfully slow because ogr updates features one by one and furthermore updates all existing attributes incl. geometries.<br>
Eg. updating 10000 features in pgAdmin/psql with UPDATE <table> SET x = 1 executes in milliseconds but takes several minutes with ogr.<br>
<br>
The current ogr functionality is also not correct from a database transactional point of view.<br>
<br>
I found an old RFC, <a href="https://gdal.org/development/rfc/rfc13_createfeatures.html" target="_blank">
https://gdal.org/development/rfc/rfc13_createfeatures.html</a>, requesting this but it was withdrawn for reasons not anymore digitally available.<u></u><u></u></p>
</div>
</div>
<div name="messageSignatureSection">
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">Best Regards<br>
<br>
Andreas Oxenstierna<br>
T-Kartor Geospatial AB<br>
Olof Mohlins väg 12 Kristianstad<br>
mobile: +46 733 206831<br>
mailto: <a href="mailto:andreas.oxenstierna@t-kartor.com" target="_blank">andreas.oxenstierna@t-kartor.com</a><br>
<a href="https://eur06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.t-kartor.com%2F&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7Ced60af7ce08c45c3998108da49e44745%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C1%7C637903542052214466%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=UNIxumUx%2BtBz8Vb7jjH3Uvnbjwo3WYvj9tsRmrFHw5g%3D&reserved=0" target="_blank">www.t-kartor.com</a><u></u><u></u></p>
</div>
</div>
</div>
</div>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</blockquote></div>