I have a dataframe stations_gdf, and I want to upload this dataframe in pgAdmin, using the following code, but I am getting error, and I dont have anz idea how could I solve that :( Can anybody help me please?
The error is DuplicateTable: relation "idx_stations_geometry" already exists
After running the following code, I am getting error:
# Set data types in PG explicitly.
dtypes = {"station_id": Numeric(6,0), "altitude" : REAL, "date_from" : Date, "date_to" : Date, "longitude" : REAL, "latitude" : REAL}
engine.execute("DROP VIEW IF EXISTS dwd.v_stations_prec")
stations_gdf.to_postgis(name="stations", schema="dwd", if_exists = "replace", index = "station_id", index_label=True, con=engine, dtype=dtypes)
engine.execute('alter table dwd.stations add primary key (station_id)')
Error:
---------------------------------------------------------------------------
DuplicateTable Traceback (most recent call last)
~\anaconda3\envs\geo\lib\site-packages\sqlalchemy\engine\base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
1801 if not evt_handled:
-> 1802 self.dialect.do_execute(
1803 cursor, statement, parameters, context
~\anaconda3\envs\geo\lib\site-packages\sqlalchemy\engine\default.py in do_execute(self, cursor, statement, parameters, context)
731 def do_execute(self, cursor, statement, parameters, context=None):
--> 732 cursor.execute(statement, parameters)
733
DuplicateTable: relation "idx_stations_geometry" already exists
The above exception was the direct cause of the following exception:
ProgrammingError Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_2184/2370298131.py in <module>
10 engine.execute("DROP VIEW IF EXISTS dwd.v_stations_prec")
11
---> 12 stations_gdf.to_postgis(name="stations", schema="dwd", if_exists = "replace", index = "station_id", index_label=True, con=engine, dtype=dtypes)
13
14 #engine.execute('alter table dwd.stations add constraint my_awesome_pkey primary key (station_id)')
~\anaconda3\envs\geo\lib\site-packages\geopandas\geodataframe.py in to_postgis(self, name, con, schema, if_exists, index, index_label, chunksize, dtype)
1806
1807 """
-> 1808 geopandas.io.sql._write_postgis(
1809 self, name, con, schema, if_exists, index, index_label, chunksize, dtype
1810 )
~\anaconda3\envs\geo\lib\site-packages\geopandas\io\sql.py in _write_postgis(gdf, name, con, schema, if_exists, index, index_label, chunksize, dtype)
429 with _get_conn(con) as connection:
430
--> 431 gdf.to_sql(
432 name,
433 connection,
~\anaconda3\envs\geo\lib\site-packages\pandas\core\generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
2870 from pandas.io import sql
2871
-> 2872 sql.to_sql(
2873 self,
2874 name,
~\anaconda3\envs\geo\lib\site-packages\pandas\io\sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
715 )
716
--> 717 pandas_sql.to_sql(
718 frame,
719 name,
~\anaconda3\envs\geo\lib\site-packages\pandas\io\sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
1749 sql_engine = get_engine(engine)
1750
-> 1751 table = self.prep_table(
1752 frame=frame,
1753 name=name,
-------------------------------------
~\anaconda3\envs\geo\lib\site-packages\sqlalchemy\engine\base.py in _run_ddl_visitor(self, visitorcallable, element, **kwargs)
2111
2112 """
-> 2113 visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
2114
2115 @util.deprecated(
~\anaconda3\envs\geo\lib\site-packages\sqlalchemy\engine\default.py in do_execute(self, cursor, statement, parameters, context)
730
731 def do_execute(self, cursor, statement, parameters, context=None):
--> 732 cursor.execute(statement, parameters)
733
734 def do_execute_no_params(self, cursor, statement, context=None):
ProgrammingError: (psycopg2.errors.DuplicateTable) relation "idx_stations_geometry" already exists
[SQL: CREATE INDEX idx_stations_geometry ON dwd.stations USING gist (geometry)]
(Background on this error at: https://sqlalche.me/e/14/f405)How can I solve this issue? I tried using different database and different schema, still, I am getting this error. What is the problem here?