PG needs to check both TypeDecorator and Variant subclasses for correct Integer subclass

The DDL compiler for the "postgresql" dialect does not render BIGSERIAL for biginteger primary keys if the column type object has been produced by ".with_variant()".

From the sample script below: I would expect that all tables "table1", "table2" and "table3" have the "key" column created as "BIGSERIAL", but only "table1" is created as such.

from __future__ import print_function

from sqlalchemy import Table, Column, Integer, BigInteger, create_engine, MetaData
from sqlalchemy.schema import CreateTable


e = create_engine("postgresql://scott:tiger@localhost/test")

meta = MetaData()

# Correctly renders as BIGSERIAL by the posgresql dialect
KeyType1 = BigInteger
# Should be rendered as BIGSERIAL by the postgresql dialect
KeyType2 = BigInteger().with_variant(Integer(), "sqlite")
# Should be rendered as BIGSERIAL by the postgresql dialect
KeyType3 = Integer().with_variant(BigInteger(), "postgresql")


table1 = Table("table1", meta,
               Column("key", KeyType1, primary_key=True))

table2 = Table("table2", meta,
               Column("key", KeyType2, primary_key=True))

table3 = Table("table3", meta,
               Column("key", KeyType3, primary_key=True))

#
# All three tables should be rendered with a BIGSERIAL on
# column "key". But only "table1" is rendered correctly.
# "table2" and "table3" are rendered with "SERIAL".
#
print(CreateTable(table1).compile(dialect=e.dialect))
print(CreateTable(table2).compile(dialect=e.dialect))
print(CreateTable(table3).compile(dialect=e.dialect))

I am currently using SQLAlchemy 1.0.13.

Comments (2)