OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Unique Constraint Violated on Empty PostgreSQL Table Using Python SQLAlchemy

  • Thread starter Thread starter GeorgeWTrump
  • Start date Start date
G

GeorgeWTrump

Guest
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:

Code:
    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. The meta table is created as such:

Code:
CREATE TABLE meta
(
    lid                     BIGINT PRIMARY KEY,
    category_int            INT                      NOT NULL,
    category_name           TEXT                     NOT NULL,
    meta_timestamp timestamp with time zone not null /* First time we scraped this lot */
);

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:

Code:
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)
<p>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.</p>
<p>I get the following error messages:</p>
<blockquote>
<p>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.</p>
</blockquote>
<p>I have the following SQL table:</p>
<pre><code> 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)
);
</code></pre>
<p>Where lid is the primary key for the meta table. The meta table is created as such:</p>
<pre><code>CREATE TABLE meta
(
lid BIGINT PRIMARY KEY,
category_int INT NOT NULL,
category_name TEXT NOT NULL,
meta_timestamp timestamp with time zone not null /* First time we scraped this lot */
);
</code></pre>
<p>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.</p>
<p>I use Pandas dataframes to insert the data as such:</p>
<pre><code>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)
</code></pre>
 
Top