Postgresql (psycopg2.errors.DuplicateTable) relation "idx_xxx

Environment:

Python 3.10
geopandas 0.10.2
Postgresql 14.2.2

Code:

import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine

x = pd.read_csv("./ships.csv")
x = gpd.GeoDataFrame(x, geometry=gpd.points_from_xy(x.LON, x.LAT))

engine = create_engine("postgresql://postgres:root@localhost/db")
x.to_postgis('ships', engine, index=False)

Result:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "idx_ships_geometry" already exists

[SQL: CREATE INDEX idx_ships_geometry ON public.ships USING gist (geometry)]
(Background on this error at: https://sqlalche.me/e/14/f405)

CodePudding user response：

this is a known bug caused by GeoAlchemy 0.11. Until this gets fixed in GeoAlchemy, use the older version 0.10.2 to make it work. See https://github.com/geopandas/geopandas/issues/2375 for details.