insert().select_from() ignores custom dialect-specific keywords

I use sqlalchemy 1.0.11 with postgresql database. I want to merge two tables and use "ON CONFLICT DO NOTHING" postgresql-specific keyword to do this. I want to do it the right way and probably want to use "ON CONFLICT" construct somewhere else in my code. So I write the following:

from sqlalchemy import Table, Column, Integer, MetaData
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert

metadata = MetaData()

tableA = Table('tableA', metadata, Column('field', Integer, primary_key=True), schema='global', extend_existing=True)
tableB = Table('tableB', metadata, Column('field', Integer, primary_key=True), schema='global', extend_existing=True)

@compiles(Insert)
def postgresql_on_conflict(insert, compiler, **kw):
    s = compiler.visit_insert(insert, **kw)
    if insert.kwargs.get('postgresql_on_conflict') is not None:
        return s + " ON CONFLICT " + insert.kwargs['postgresql_on_conflict']
    return s

Insert.argument_for('postgresql', 'on_conflict', None)

print tableA.insert(postgresql_on_conflict='DO NOTHING').from_select(tableB.c, tableB.select()).compile()

And it produces the following output:

INSERT INTO general."tablea" (field) SELECT field 
FROM general."tableb"

The postgresql_on_conflict keyword works fine by itself.

UPD. Changed minimal example, now it's runnable

UPD. It looks like minimal example works as it should (except it doesn't add brackets to the SELECT query, which also, probably, can be changed), but in my project almost the same code misses the "ON CONFLICT" part.

Comments (6)