0

I am trying trying to delete duplicates from Redshift table

Mycode

from sqlalchemy import create_engine
# A long string that contains the necessary Postgres login information
postgres_str = f'postgresql://{redshift_username}:{redshift_password}@{redshift_address}:{redshift_port}/{redshift_dbname}'
# Create the connection
cnx = create_engine(postgres_str)


delstatmt = '''WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY org_country_code,dest_country_code,postcode,zone,kg,value,carrier,version 
ORDER BY  org_country_code,dest_country_code,postcode,zone,kg,value,carrier,version ) AS RN
FROM d.axis
)

DELETE FROM d.axis transformed WHERE RN<>1'''

cnx.execute(delstatmt)

Error

ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "DELETE"

LINE 8: DELETE FROM d.axis transformed WHERE ...

What is wrong is code. Any help appreciated.

aeapen
  • 871
  • 1
  • 14
  • 28
  • Your connection URI begins with `postgresql://`. Why are you using the PostgreSQL dialect when there is a [Redshift dialect](https://pypi.org/project/sqlalchemy-redshift/) that you can use? – Gord Thompson Jul 24 '20 at 15:14
  • @Gord Thompson, this works for me also I am able read from the table using this connection . Issue is with delete query – aeapen Jul 24 '20 at 15:38
  • 1
    Your DELETE statement does not reference the CTE (e.g., [via USING](https://stackoverflow.com/a/31731637/2144390)) so I would expect `RN` to be an unknown column name. – Gord Thompson Jul 24 '20 at 17:25

0 Answers0