Description
It seems Redshift only supports a leading INSERT INTO statement followed by a query containing a CTE whereas PostgreSQL supports both. I.e.:
drop table if exists whosonfirst;
create table whosonfirst
(
my_id integer
) ;
-- works in postgres and in redshift
insert into whosonfirst
with what as (
select 1 as my_id
)
select * from what;
-- only works in postgres
with what as (
select 1 as my_id
)
insert into whosonfirst
select * from what
-- one row in redshift, 2 rows in postgre
select * from whosonfirstSQLAlchemy has a flag to force the former: cte_follows_insert. Would it be possible to get RedshiftDialect to override cte_follows_insert to True?
See also the original SQLAlchemy issue that flipped the order, the SQLAlchemy issue addin the cte_follows_insert functionality and the SQLAlchemy commit which introduced the flag.