<div dir="ltr"><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Hello!</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">I'm having trouble using the temporary table option of ogr2ogr:<span class="gmail-Apple-converted-space"> </span><a href="https://gdal.org/drivers/vector/pg.html#layer-creation-options" title="https://gdal.org/drivers/vector/pg.html#layer-creation-options">https://gdal.org/drivers/vector/pg.html#layer-creation-options</a></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Ultimately I am trying to load data from a Shapefile into a temporary table, enrich the rows in the temporary table, and then copy from the temporary table into a final table. However I cannot get Postgres and ogr2ogr to work together. It sounds to me from the logs that they disagree on the table name. In my example code below I have removed everything that is to happen after loading the data (enriching, copying) for clarity.</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Very little is said about the temporary table option on the web, but from the source (and Postgres logs) it looks like ogr2ogr assumes a schema of "pg_temp_1":<span class="gmail-Apple-converted-space"> </span><a href="https://github.com/OSGeo/gdal/blob/dc39f7d68095621842a2547ab06c60a03fd34a19/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L1865-L1872" title="https://github.com/OSGeo/gdal/blob/dc39f7d68095621842a2547ab06c60a03fd34a19/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L1865-L1872">https://github.com/OSGeo/gdal/blob/dc39f7d68095621842a2547ab06c60a03fd34a19/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp#L1865-L1872</a></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">It sounds to me from the Postgres docs this isn't safe to assume, but let's say it is for the moment.</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Here is my simplified script:</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">#!/bin/bash</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">set -e</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGHOST="<host>"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGDATABASE="<db>"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGUSER="<user>"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGPASSWORD="<pass>"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PGPORT=<port></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">LOCAL_FILE="/vsizip/test.shp.zip"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">SRC_LAYER="test"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">FID_NAME="gfw_fid"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">GEOMETRY_NAME="geom"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">TEMP_SCHEMA="pg_temp_1"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">TEMP_TABLE="bazinga"</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">ogr2ogr -f "PostgreSQL" PG:"password=$PGPASSWORD host=$PGHOST port=$PGPORT dbname=$PGDATABASE user=$PGUSER" \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    "$LOCAL_FILE" "$SRC_LAYER" \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -lco GEOMETRY_NAME="$GEOMETRY_NAME" \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -lco SPATIAL_INDEX=NONE \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -lco FID="$FID_NAME" \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -lco TEMPORARY=ON \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -nlt PROMOTE_TO_MULTI \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -nln "$TEMP_SCHEMA.$TEMP_TABLE" \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">   -t_srs EPSG:4326 \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    --config PG_USE_COPY YES \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -makevalid -update</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"># End of script</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Here is the Postgres log from attempting to run this script with the Shapefile in the PS:</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"># START LOG</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.206 UTC [504] LOG:  statement: set client_encoding to 'UTF8'</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.209 UTC [504] LOG:  execute <unnamed>: SHOW client_encoding</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.239 UTC [504] LOG:  execute <unnamed>: SELECT n.nspname FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE proname = 'postgis_version'</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.245 UTC [504] LOG:  execute <unnamed>: SELECT version()</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.246 UTC [504] LOG:  execute <unnamed>: SHOW standard_conforming_strings</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.250 UTC [504] LOG:  execute <unnamed>: SELECT oid, typname FROM pg_type WHERE typname IN ('geometry', 'geography') AND typtype='b'</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.323 UTC [504] LOG:  execute <unnamed>: SELECT postgis_version()</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.326 UTC [504] LOG:  execute <unnamed>: SET ENABLE_SEQSCAN = ON</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.342 UTC [504] LOG:  execute <unnamed>: SELECT 1 FROM information_schema.tables WHERE table_name = 'geometry_columns' LIMIT 1</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.345 UTC [504] LOG:  execute <unnamed>: SELECT 1 FROM information_schema.tables WHERE table_name = 'spatial_ref_sys' LIMIT 1</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.354 UTC [504] LOG:  execute <unnamed>: SELECT ST_Srid('POINT EMPTY'::GEOMETRY)</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.356 UTC [504] LOG:  execute <unnamed>: SELECT current_schema()</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.394 UTC [504] LOG:  execute <unnamed>: BEGIN</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.415 UTC [504] LOG:  execute <unnamed>: SELECT c.oid FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.relname = 'bazinga' AND n.nspname = 'pg_temp_1'</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.418 UTC [504] LOG:  execute <unnamed>: SELECT c.oid FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.relname = 'bazinga' AND n.nspname = 'pg_temp_1'</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.439 UTC [504] LOG:  execute <unnamed>: SELECT c.relname, n.nspname, c.relkind, a.attname, t.typname, postgis_typmod_dims(a.atttypmod) dim, postgis_typmod_srid(a.atttypmod) srid, postgis_typmod_type(a.atttypmod)::text geomtyp, array_agg(pg_get_constraintdef(s.oid))::text att_constraints, a.attnotnull, d.description FROM pg_class c JOIN pg_attribute a ON a.attrelid=c.oid JOIN pg_namespace n ON c.relnamespace = n.oid AND c.relkind in ('r','v','m','f') AND NOT ( n.nspname = 'public' AND c.relname = 'raster_columns' ) JOIN pg_type t ON a.atttypid = t.oid AND (t.typname = 'geometry'::name OR t.typname = 'geography'::name) LEFT JOIN pg_constraint s ON s.connamespace = n.oid AND s.conrelid = c.oid AND a.attnum = ANY (s.conkey) AND (pg_get_constraintdef(s.oid) LIKE '%geometrytype(% = %' OR pg_get_constraintdef(s.oid) LIKE '%ndims(% = %' OR pg_get_constraintdef(s.oid) LIKE '%srid(% = %') LEFT JOIN pg_description d ON d.objoid = c.oid AND d.classoid = 'pg_class'::regclass::oid AND d.objsubid = 0 GROUP BY c.relname, n.nspname, c.relkind, a.attname, t.typname, dim, srid, geomtyp, a.attnotnull, c.oid, a.attnum, d.description ORDER BY c.oid, a.attnum</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.452 UTC [504] LOG:  execute <unnamed>: SELECT srid FROM spatial_ref_sys WHERE auth_name = 'EPSG' AND auth_srid = 4326</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.545 UTC [504] LOG:  execute <unnamed>: CREATE TEMPORARY TABLE "bazinga" ( "gfw_fid" SERIAL, PRIMARY KEY ("gfw_fid"), "fid" NUMERIC(11,0), "geom" geometry(MULTIPOLYGON,4326) )</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.575 UTC [504] LOG:  execute <unnamed>: COMMENT ON TABLE "pg_temp_1"."bazinga" IS NULL</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.575 UTC [504] ERROR:  relation "pg_temp_1.bazinga" does not exist</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.575 UTC [504] STATEMENT:  COMMENT ON TABLE "pg_temp_1"."bazinga" IS NULL</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.585 UTC [504] ERROR:  current transaction is aborted, commands ignored until end of transaction block</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.585 UTC [504] STATEMENT:  COPY "pg_temp_1"."bazinga" ("geom", "fid") FROM STDIN;</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">2022-11-28 21:16:51.587 UTC [504] LOG:  execute <unnamed>: ROLLBACK</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"># END LOG</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">I have tried many variations of quoting the table and schema name to no avail (including</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -nln "\"$TEMP_SCHEMA\".\"$TEMP_TABLE\"" \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">).</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">I also tried wrapping the whole thing in a transaction by specifying</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -doo PRELUDE_STATEMENTS="BEGIN;" \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">    -doo CLOSING_STATEMENTS="COMMIT;" \</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Can anyone explain how to get this to work? I actually first tried to create a temporary table by myself with -doo, but ran into other problems and will leave that for another thread.</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Thanks!</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">Daniel Mannarino</p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><br></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PS: Here's the Shapefile I'm using:<span class="gmail-Apple-converted-space"> </span><a href="https://github.com/wri/gfw-data-api/blob/master/tests/fixtures/test.shp.zip" title="https://github.com/wri/gfw-data-api/blob/master/tests/fixtures/test.shp.zip">https://github.com/wri/gfw-data-api/blob/master/tests/fixtures/test.shp.zip</a></p><p class="MsoNormal" style="margin:0in;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)">PPS: I am running PostgreSQL 12 (specifically the postgis/postgis:12-2.5-alpine Docker image) and running ogr2ogr from Homebrew's GDAL 3.5.3 package on MacOS 12.6 (amd64).</p></div>