I am trying to insert data into an empty table. I can insert my dataframes for the other tables just fine, but I cannot insert it into my shipping table, which is also the only one with a unique constraint.

I get the following error messages:

IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint “shipping_lid_price_currency_code_key”
DETAIL: Key (lid, price, currency_code)=(85238091, 120.0, EUR) already exists.

I have the following SQL table:

    CREATE TABLE shipping
(
  sid SERIAL PRIMARY KEY,
  lid BIGINT NOT NULL,
  region_code TEXT NOT NULL,
  region_name TEXT NOT NULL,
  price NUMERIC NOT NULL CHECK (price >= 0),
  currency_code CHAR(3) CHECK (currency_code IN ('USD', 'EUR', 'GBP')),
  estimated_delivery_from_days INT NOT NULL, /* Earliest number of days to deliver */
  estimated_delivery_to_days INT NOT NULL, /* Latest number of days to deliver */
  destination_country_name TEXT NOT NULL,
  destination_country_short_code TEXT NOT NULL,
  combined_shipping_allowed BOOL NOT NULL,
  delivery_methods TEXT NOT NULL,
  extra_insurance BOOLEAN NOT NULL,
  provider_id INT NOT NULL,
  is_pickup_preferable BOOLEAN NOT NULL,
  is_pickup_only BOOLEAN NOT NULL,
  pickup_location_country_code TEXT,
  pickup_location_city TEXT,
  shipping_timestamp TIMESTAMP WITH TIME ZONE NOT NULL, /* Timestamp for when we request the shipping API */
  FOREIGN KEY (lid) REFERENCES meta (lid) ON DELETE CASCADE,
  UNIQUE (lid, price, destination_country_short_code,currency_code)
);

Where lid is the primary key for the meta table.

I am able to correctly insert data into all the other tables in my database, and I am occasionally able to insert data into the shipping table. Although there seemed to be no common denominator between the data I was able to insert, that could differentiate it from my other data.

I use Pandas dataframes to insert the data as such:

engine = create_engine('postgresql://DATABASE:PASSWORD@localhost:5432/USERNAME')
Session = sessionmaker(bind=engine)
session = Session()

"""
SECRET CODE THAT GENERATES MY DATAFRAME

"""

df.to_sql(record_key.replace("_record",""), con=engine, if_exists='append',index=False)