How to Append data in existing postgres table with incrementing primary key using python df._topostgis?

I have created a table in postgresql,

in which I am dumping the data of Geopandas GoeDataFrame.

After dumping it, I am assigning/making the column named "fid" as the primary key column.

and I have to update this table daily.

When I am replacing the table, then it is working fine

name_Database = 'agro_dss';    def update_table():      engine_url = "postgresql://postgres:imd123#@localhost:5432/"+name_Database      engine = create_engine(engine_url)      df_pg = input_df      df_update = gpd.GeoDataFrame(df_pg)      df_update.to_postgis(con=engine, name='rainfall_forecast', if_exists = 'replace', index=True, index_label = "fid")  update_table()  

But I want to keep the previous data too, and append the new data in the same table.

The structure/columns in new dataset are same as previous day,

so there should be no issue to append the similar datasets rows

below the existing previous rows.

for doing so, I am changing if_exists conditon from 'replace' to 'append' in the code block.

df_update.to_postgis(con=engine, name='rainfall_forecast', if_exists = 'append', index=True, index_label = "fid")  

but its assigning the same index value (starting from zero to n number of rows) to the fid every time I append, due to which I am not able to set the 'fid' column as primary key

conn = psycopg2.connect(user='postgres', password='your_password', host='localhost', port= '5432', dbname='agro_dss')  conn.autocommit = True  cursor = conn.cursor()  sql_primary_key = "ALTER TABLE rainfall_forecast ADD PRIMARY KEY (fid)";  cursor.execute(sql_primary_key)  

as it throws this error

---------------------------------------------------------------------------  UniqueViolation                           Traceback (most recent call last)  <ipython-input-36-22d992415e7c> in <module>        6 sql_primary_key = "ALTER TABLE rainfall_forecast ADD PRIMARY KEY (fid)";  ----> 7 cursor.execute(sql_primary_key)    UniqueViolation: could not create unique index "rainfall_forecast_pkey"  DETAIL:  Key (fid)=(3487) is duplicated.  

How to keep the fid column unique, so that it assign integers to the column "fid" in increasing order for the new data being inserted ?

https://stackoverflow.com/questions/65541342/how-to-append-data-in-existing-postgres-table-with-incrementing-primary-key-usin January 03, 2021 at 12:18AM