[gdal-dev] ogr2ogr postgis to sqlite composite primary key issueissue

[Scott]

Change target.gpkg to target.sqlite

...and in the -sql use something like "select cast(primary_key as 
integer) as key, [all your other columns] from table"


> Thanks,
> 
> The primary key on the source tables is on required columns - it is a 
> natural key based on core data so cannot be excluded to have a 
> meaningful dataset exported.
> 
> Also, invoking it your way provides a data only export - just the output 
> of a select statement. I'm trying to create replicate data structures 
> populated with the data in the Spatialite database.
> 
> It seems that using an intermediate format such as shapefile should 
> work, as this will essentially drop any primasry key anyway, but I'm 
> trying the direct approach first.
> 
> Also, your example exports to geopackage, that is not what I'm after, 
> Spatialite is much more than geopackage.
> 
> Cheers
> 
> Brent
> 
> On Wednesday, August 16, 2023 at 12:57:14 PM GMT+12, Scott 
> <public at postholer.com> wrote:
> 
> 
> You could build your column list without that specific column and pass
> it to ogr2ogr (bash):
> 
> ogr2ogr -sql "select $(psql -d mydb --pset='footer=off' -F',' -qAc
> "select * from table where false" |sed 's/columnToOmit,//g') from table"
> target.gpkg PG:dbname=mydb
> 
> 
> On 8/15/23 17:12, Brent Wood via gdal-dev wrote:
>  > Hi,
>  >
>  > I'm looking to use ogr2ogr to replicate a Postgis schema into a
>  > Spatialite database. (Make a snapshot copy of institutional tables on a
>  > field laptop with possible no internet access)
>  >
>  > I have a script to identify the relevant tables and invoke ogr2ogr to
>  > copy each table.
>  >
>  > Some of the source (Postgis) tables have a composite primary key.
>  > ogr2ogr flags these and says such keys are unsupported. It fails to copy
>  > these tables.
>  >
>  > What I'd like it to do is copy the data in such cases but ignore the
>  > primary key.
>  >
>  > Is this possible?
>  >
>  >
>  > Thanks,
>  >
>  > Brent Wood
>  >
>  > _______________________________________________
>  > gdal-dev mailing list
>  > gdal-dev at lists.osgeo.org <mailto:gdal-dev at lists.osgeo.org>
>  > https://lists.osgeo.org/mailman/listinfo/gdal-dev 
> <https://lists.osgeo.org/mailman/listinfo/gdal-dev>
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org <mailto:gdal-dev at lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/gdal-dev 
> <https://lists.osgeo.org/mailman/listinfo/gdal-dev>