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

Question

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 ?

question from:https://stackoverflow.com/questions/65541342/how-to-append-data-in-existing-postgres-table-with-incrementing-primary-key-usin

Answer 1

The task basically requires you to clean up any existing duplicate values in the column that is to become the PK, and identifying the maximum value of that column. That will be necessary anyway, so NO extra work. I am unable to write the necessary code in your ORM as I don't know it. Even if I could I would not, the task requires 4 commands - not worth writing code for. Talk to your DBA if needed.

create sequence <sequence for pk> start <current max id + some>;
alter table rainfall_forecast alter column fid set default nextval('<sequence for pk>'); 
alter table rainfall_forecast add constraint rainfall_forecast_pk  primary key (fid) ; 
alter sequence <sequence for pk> owned by rainfall_forecast.fid;

Replace <sequence for pk> with the sequence name to be used.
Replace <current max id + some> with the desired starting PK value. I would use the current max(fid) + 100 or so. This will likely leave a gap in the id, but that is ok, They will occur anyway, you cannot stop it, so just embrace it and move on. See example here.