How to correctly write the index as a primary key when exporting a GeoDataFrame to PostGIS

Question

I'm trying to write a GeoDataFrame containing some POLYGON Z features directly to a PostGIS table which already has the right structure, especially a PolygonZ geometry column. This works fine:

G.to_postgis('test', engine, if_exists='append')

In a first attempt, psycopgg2 had trouble finding the SRID:

InternalError: (psycopg2.errors.RaiseException) find_srid() - could not find the    
corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table?    
Is there an uppercase/lowercase mismatch?    
CONTEXT:  PL/pgSQL function find_srid(character varying,character varying,    
character varying) line 17 at RAISE

[SQL: SELECT Find_SRID('public', 'test', 'geometry');]
(Background on this error at: http://sqlalche.me/e/2j85)

This was because the name of my the geometry field in my GeoDataFrame didn't match the one in PostGIS!
They have to match.

The test table SQL code looks like this in pgAdmin:

-- Table: public.test

-- DROP TABLE public.test;

CREATE TABLE IF NOT EXISTS public.test
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( 
        INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 
    ),
    geometry geometry(PolygonZ,2056)
)

TABLESPACE pg_default;

ALTER TABLE public.test
    OWNER to postgres;

Now, if I write to a table which doesn't already exist, GeoPandas will create it:

G.to_postgis('test2', engine, if_exists='replace') #table 'test2' doesn't already exist

That's cool, but then, the SQL code associated to this table looks like:

-- Table: public.test2

-- DROP TABLE public.test2;

CREATE TABLE IF NOT EXISTS public.test2
(
    geometry geometry(PolygonZ,2056)
)

TABLESPACE pg_default;

ALTER TABLE public.test2
    OWNER to postgres;
-- Index: idx_test2_geometry

-- DROP INDEX public.idx_test2_geometry;

CREATE INDEX idx_test2_geometry
    ON public.test2 USING gist
    (geometry)
    TABLESPACE pg_default;

There is no id column (which I expected as the primary key constraint). It could have simply be taken from the GeoDataFrame index if such index also meet the two conditions of a RDB primary key: it is unique and it cannot be null.

This seems actually possible using the index option:

G.to_postgis('test2', engine, if_exists='replace', index=True)

which leads to:

-- Table: public.test2

-- DROP TABLE public.test2;

CREATE TABLE IF NOT EXISTS public.test2
(
    index bigint,
    geometry geometry(PolygonZ,2056)
)

TABLESPACE pg_default;

ALTER TABLE public.test2
    OWNER to postgres;
-- Index: idx_test2_geometry

-- DROP INDEX public.idx_test2_geometry;

CREATE INDEX idx_test2_geometry
    ON public.test2 USING gist
    (geometry)
    TABLESPACE pg_default;
-- Index: ix_public_test2_index

-- DROP INDEX public.ix_public_test2_index;

CREATE INDEX ix_public_test2_index
    ON public.test2 USING btree
    (index ASC NULLS LAST)
    TABLESPACE pg_default;

but as the index option is a boolean which default value is True, I would have expected the exact same behavior by specifying index=True or not.
Indeed, the index column is created only when this option is manually specified.
If it's not, there is only the geometry column (no index).

But even when the index field is present, it does not appear to be a primary key as when the table was created manually.

Hence my question: is it possible to create a new table with a primary key corresponding to the index of the GeoDataFrame when using GeoDataFrame.to_postgis() method? I cannot figure it out on the documentation.

Versioning:

OS: Ubuntu 18.04
Python: 3.6.9

geopandas:   0.9.0
psycopg2:    2.9.1 (dt dec pq3 ext lo64)
sqlalchemy:  1.3.13
geoalchemy2: 0.9.2

PostgreSQL: 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu

PostGIS full version:

POSTGIS="3.1.2 cbe925d" [EXTENSION]
 PGSQL="130"
 GEOS="3.7.1-CAPI-1.11.1 27a5e771"
 PROJ="Rel. 5.2.0, September 15th, 2018"
 LIBXML="2.9.4"
 LIBJSON="0.12.1"
 LIBPROTOBUF="1.3.1"
 WAGYU="0.5.0 (Internal)" TOPOLOGY

Answer 1

You won't figure this problem out by reading the documentation. In fact, I don't think there is a solution to this problem. GeoDataFrame.to_postgis() converts the GeoDataFrame to a regular pandas DataFrame and then calls DataFrame.to_sql(). The to_sql method doesn't seem to support creation of primary keys (see here or here). And there also doesn't seem to be much activity on the related github issue.

I took the manual creation of a primary key with an alter table .. add primary key .. statement as a last resort.