Setting the id column as a PostgreSQL identity field when inserting data into PostGIS with ogr2ogr

Question

I want to tell my ogr2ogr command which inserts data from a ESRI Shapefile into a PostgreSQL/PostGIS database to create the id field as a PostgreSQL IDENTITY column (not as a SERIAL).

For the moment, my command:

$ ogr2ogr -f "PostgreSQL" PG:"postgresql://postgres:password@localhost:5432/mydatabase" \
 myshapefile.shp \
 -lco GEOMETRY_NAME=geom \
 -lco FID=id \
 -lco SPATIAL_INDEX=GIST \
 -nlt PROMOTE_TO_MULTI \
 -nln foo \
 -overwrite

creates the table as follow, creating the id as a SERIAL:

-- Table: public.foo
-- DROP TABLE IF EXISTS public.foo;
CREATE TABLE IF NOT EXISTS public.foo
(
 id integer NOT NULL DEFAULT nextval('foo_id_seq'::regclass),
 fid_part numeric(12,0),
 orient numeric(4,0),
 geom geometry(MultiLineStringZ),
 CONSTRAINT foo_pkey PRIMARY KEY (id)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.foo
 OWNER to postgres;
-- Index: foo_geom_geom_idx
-- DROP INDEX IF EXISTS public.foo_geom_geom_idx;
CREATE INDEX IF NOT EXISTS foo_geom_geom_idx
 ON public.foo USING gist
 (geom)
 TABLESPACE pg_default;

But if I create the same table using pg:

CREATE TABLE IF NOT EXISTS public.foo
(
 id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
 fid_part numeric(12,0),
 orient numeric(4,0),
 geom geometry(MultiLineStringZ)
)

it creates the table as:

-- Table: public.foo
-- DROP TABLE IF EXISTS public.foo;
CREATE TABLE IF NOT EXISTS public.foo
(
 id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
 fid_part numeric(12,0),
 orient numeric(4,0),
 geom geometry(MultiLineStringZ),
 CONSTRAINT foo_pkey PRIMARY KEY (id)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.foo
 OWNER to postgres;

How could I achieve the same result with ogr2ogr?

Answer 1

You can't create FID as identity with ogr2ogr. In the source code https://github.com/OSGeo/gdal/blob/master/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp the alternatives for the datatype of FID are either SERIAL (default) or BIGSERIAL (if layer creation option FID64 is TRUE).

/* -------------------------------------------------------------------- */
/* Create a basic table with the FID. Also include the */
/* geometry if this is not a PostGIS enabled table. */
/* -------------------------------------------------------------------- */
 const bool bFID64 = CPLFetchBool(papszOptions, "FID64", false);
 const char* pszSerialType = bFID64 ? "BIGSERIAL": "SERIAL";

Of course if you only want to create the table with custom parameters it is possible to do with ogrinfo that can execute any SQL.

ogrinfo PG:"host=localhost port=5432 dbname=my_db user=user password=pw" \
 -sql "CREATE TABLE IF NOT EXISTS public.foo (
 id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
 fid_part numeric(12,0),
 orient numeric(4,0),
 geom geometry(MultiLineStringZ),
 CONSTRAINT foo_pkey PRIMARY KEY (id)
 )
 TABLESPACE pg_default;"

Once the table is created it is possible to insert data into now existing table with ogr2ogr by using option -append. I made a simple test and it seemed to be successful. New rows were inserted with automatically generated id's.