Postgres syntax error on a DELETE query
<p>I've successfully run this query as a <code>SELECT</code> using DataGrip directly against the specified table, but when I try to run it as a <code>DELETE</code>, it's resulting in a syntax error.</p>

<p>Code:</p>

<pre><code>conn = psycopg2.connect(connection_str.format(*connection_args))

with conn, conn.cursor() as curs:
    delete_duplicates_command = """
            DELETE FROM (SELECT *, row_number() OVER (PARTITION BY {pk} 
                ORDER BY _ts DESC) as row_num
            FROM {table}) as t
            WHERE t.row_num != 1;
        """.format(table=target_table, pk=primary_key)

    curs.execute(delete_duplicates_command)
</code></pre>

<p>Error:</p>

<pre><code>Migration failed: syntax error at or near "("
    LINE 2:             DELETE FROM (SELECT *, row_number() OVER (PARTIT...
                                    ^
</code></pre>

<p>Both the table and primary key are valid string values. I've tried reformatting this command multiple ways ways to no avail.</p>

<p>FYI I'm using <code>psycopg2==2.6</code> and <code>Python 2.7.3</code></p>

<hr>

<p>Trying to rewrite the query to avoid doing a select inside the delete:</p>

<pre><code>WITH ordered_table AS
    (SELECT *, row_number() OVER (PARTITION BY pk ORDER BY _ts DESC) as row_num
        FROM orig_table)
DELETE FROM orig_table
USING ordered_table
WHERE ordered_table.row_num &gt; 1;
</code></pre>

<p>Which results in the follow error:
<code>ERROR: syntax error at or near "DELETE"</code></p>

<p>from the docs:</p>

<pre><code>cur.execute(
     """INSERT INTO some_table (an_int, a_date, another_date, a_string)
         VALUES (%(int)s, %(date)s, %(date)s, %(str)s);""",
     {'int': 10, 'str': "O'Reilly", 'date': datetime.date(2005, 11, 18)})
</code></pre>

<p>so, for you:</p>

<pre><code>cur = conn.cursor()

query = """DELETE FROM (SELECT *, row_number() OVER (PARTITION BY (%(pk)s) 
                  ORDER BY _ts DESC) as row_num
               FROM (%(table)s) as t
               WHERE t.row_num != 1;"""

cur.execute(query, {'pk': whateverpkis, 'table': whatevertableis})
</code></pre>

<p><a href="http://initd.org/psycopg/docs/usage.html" rel="nofollow">http://initd.org/psycopg/docs/usage.html</a></p>

python postgresql amazon-redshift psycopg2