Redshift's delete statement varies slightly from Postgresql's. See here for documentation.
Basic delete statements have the same syntax.
For instance, the following is valid SQL in both dialects:
DELETE FROM customer_table WHERE customer_table.id > 1000
However, while the following is a valid statement in Postgresql:
DELETE FROM customer_table
WHERE customer_table.id = order_table.customer_id
AND order_table.id < 100
It needs to be written for Redshift as:
DELETE FROM customer_table
USING order_table
WHERE customer_table.id = order_table.customer_id
AND order_table.id < 100
SqlAlchemy should be able to build this resultant query with the following Python snippet:
from sqlalchemy import delete, Table, Column, Integer, MetaData
from redshift_sqlalchemy import RedshiftDialect
meta = MetaData()
customer = Table('customer_table', meta, Column('id', Integer, primary_key=True))
order = Table('order_table', meta, Column('id', Integer, primary_key=True), Column('customer_id', Integer)
del_stmt = delete(order).where(order.c.customer_id==customer.c.id).where(order.c.id<100)
print(del_stmt.compile(dialect=RedshiftDialect())
Redshift's delete statement varies slightly from Postgresql's. See here for documentation.
Basic delete statements have the same syntax.
For instance, the following is valid SQL in both dialects:
However, while the following is a valid statement in Postgresql:
It needs to be written for Redshift as:
SqlAlchemy should be able to build this resultant query with the following Python snippet: