ON CONFLICT DO UPDATE syntax and EXCLUDED error on cursor.executemany

I have a simplified postgres (ver 13) table below with updated rows generated in python with psycopg2. My question is when I update the price field in the rows, I can’t complete the update because of the following errors of ON CONFLICT DO UPDATE. If I don’t use ON CONFLICT DO UPDATE , I can update the chart but I would like ON CONFLICT DO UPDATE because it eliminates duplicate rows.

With ON CONFLICT DO UPDATE , I only need to update the fields “price” and “last_updated” but update only when the rows match the “id,item,original_price_date”

The following errors I get ON CONFLICT DO UPDATE :

Error : syntax error at or near “=”

# update the prices within the existing data
df = pd.DataFrame(np.array([['5/3/2010', 'rock', 15],
                            ['4/15/2010', 'paper', 11],
                            ['2/3/2015', 'scissor', 13]]),
                             columns = ['original_price_date', 'item', 'price'])
tuples_for_dB = [tuple(x) for x in df.to_numpy()]
sql_script = '''INSERT INTO ''' + TABLE_ + ''' (
  original_price_date, item, price, created_date, last_updated) 
  VALUES (%s, %s, %s, transaction_timestamp(), transaction_timestamp())
  ON CONFLICT (id, item, original_price_date)
  DO UPDATE SET (price, last_updated = EXCLUDED.price, EXCLUDED.transaction_timestamp());'''

Error : relation “price_data” does not exist

sql_script = '''INSERT INTO ''' + TABLE_ + ''' (
  original_price_date, item, price, created_date, last_updated) 
  VALUES (%s, %s, %s, transaction_timestamp(), transaction_timestamp())
  ON CONFLICT (id, item, original_price_date)
  DO UPDATE SET (price, last_updated) = (EXCLUDED.price, EXCLUDED.transaction_timestamp());'''

My original creation of the data :

   # postGRESQL connection details
DATABASE_INITIAL_ = 'postgres'
DATABASE_ = 'data'
TABLE_ = 'price_data'
USER_ = 'postgres'
SERVERNAME_ = 'localhost'
PASSWORD_ = password_
HOST_ = '127.0.0.1'
PORT_ = '5432'

#establishing the connection
conn = psycopg2.connect(database = DATABASE_,
                        user = USER_,
                        password = PASSWORD_,
                        host = HOST_,
                        port = PORT_);
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);
conn.autocommit = True

# Creating a cursor object using the cursor() method
cursor = conn.cursor()

sql = "SELECT 1 FROM pg_catalog.pg_database WHERE datname = " + "'" + DATABASE_ + "'"
cursor.execute(sql)

# If dB does not exist create the dB
exists = cursor.fetchone()
print(exists)
if not exists:
    print('does not exist')
    #Preparing query to create a database
    sql = '''CREATE database '''+DATABASE_;

    #Creating a database
    cursor.execute(sql)

# Creating the table
sql = '''CREATE TABLE IF NOT EXISTS ''' + TABLE_ + ''' (
  id SERIAL PRIMARY KEY,
  original_price_date DATE NOT NULL,
  item TEXT NOT NULL,
  price NUMERIC NULL DEFAULT NULL,
  created_date TIMESTAMPTZ NULL DEFAULT TRANSACTION_TIMESTAMP(),
  last_updated TIMESTAMPTZ NULL DEFAULT TRANSACTION_TIMESTAMP());'''
cursor.execute(sql)

# update the table with data
df = pd.DataFrame(np.array([['5/3/2010', 'rock', 0.9],
                            ['4/15/2010', 'paper', 6.5],
                            ['2/3/2015', 'scissor', 3.9],
                            ['3/23/2017', 'ball', 1.1],
                            ['4/7/2013', 'tire', 5.4]]),
                             columns = ['original_price_date', 'item', 'price'])
tuples_for_dB = [tuple(x) for x in df.to_numpy()]
sql_script = '''INSERT INTO ''' + TABLE_ + ''' (
  original_price_date, item, price, created_date, last_updated) 
  VALUES (%s, %s, %s, transaction_timestamp(), transaction_timestamp());'''

try:
    cursor.executemany(sql_script, tuples_for_dB);                
    success = True
except psycopg2.Error as e:
    error = e.pgcode
    print(f'Error : {e.args[0]}')
    success = False
if success:
    print(f'nData inserted successfully........')      
    print(f'Table INSERT sql commit comment :n"{sql_script}"n')
elif success == False:
    print(f'nData NOT inserted successfully XXXXXX')      
            
# Preparing query to drop a table
sql = '''DROP TABLE IF EXISTS ''' + TABLE_ + ";"
# Creating the table
cursor.execute(sql)

conn.close()

Answer

I added a constraint row (CONSTRAINT com UNIQUE (original_price_date,item))) where I created the table.

sql = '''CREATE TABLE IF NOT EXISTS ''' + TABLE_ + ''' (
  id SERIAL PRIMARY KEY,
  original_price_date DATE NOT NULL,
  item TEXT NOT NULL,
  price NUMERIC NULL DEFAULT NULL,
  created_date TIMESTAMPTZ NULL DEFAULT TRANSACTION_TIMESTAMP(),
  last_updated TIMESTAMPTZ NULL DEFAULT TRANSACTION_TIMESTAMP(),
  CONSTRAINT com UNIQUE (original_price_date,item));'''

Then I could insert the data NOT creating duplicate rows of (original_price_date,item) by the following statement.

 sql = '''INSERT INTO ''' + TABLE_ + '''(original_price_date, item, price) 
                VALUES (%s, %s, %s)
                ON CONFLICT (original_price_date, item) 
                DO UPDATE 
                    SET (price, last_updated)  = (EXCLUDED.price,TRANSACTION_TIMESTAMP());'''

Advertisement

Answer