Alter ADD Column with postgresql server_default

[sqlalchemy-bot]

Migrated issue, originally created by AlanB (@aboudreault)

I have a simple model:

class Client(Base, BaseMixin):

    __tablename__ = 'clients'
    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    nom = sa.Column(sa.UnicodeText, nullable=False, unique=True)
    creation_date =  sa.Column(
        sa.TIMESTAMP(timezone=True),
        default=sa.func.now(),
        server_default=sa.func.now(),
        nullable=False
        )

I then changed the name of the column creation_date and run alembic --autogenerate. This is my migration downgrade section generated:

    op.add_column(u'clients', sa.Column(u'creation_date', postgresql.TIMESTAMP(timezone=True), server_default=u"2013-03-15 14:08:35.674431+00'::timestamp with time zone", nullable=False))
    op.drop_column(u'clients', 'date_creation')

This is wrong and produce the following PG error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) unterminated quoted string at or near "' NOT NULL"
LINE 1: ...3-15 14:08:35.674431+00'::timestamp with time zone' NOT NULL
                                                                                                  ^
 "ALTER TABLE clients ADD COLUMN creation_date TIMESTAMP WITH TIME ZONE DEFAULT '2013-03-15 14:08:35.674431+00'::timestamp with time zone' NOT NULL" {}

[sqlalchemy-bot]

AlanB (@aboudreault) wrote:

Also, with something like this:

server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"),

alembic generates:

sa.Column('date_creation', sa.TIMESTAMP(timezone=True), server_default="TIMEZONE('utc', CURRENT_TIMESTAMP)", nullable=False),

<--- no sa.text.

So PG fails.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this is almost a dupe of #73, but the way its sticking the hardcoded date in there seems to be an additional twist.

the PG default issue is not going to be fixed easily as someone will need to write a whole parser for it. For now, consider these candidate migrations and correct the downgrades as needed.

[sqlalchemy-bot]

AlanB (@aboudreault) wrote:

Thank you for you quick answer. I'm going to fix my migration files manually for now.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: autogenerate - rendering

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

see also #64

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: autogenerate - rendering
• added labels: autogenerate - server defaults

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Duplicate of #197.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: duplicate
• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

both behaviors were fixed by #197:

    op.add_column('t', sa.Column('p', sa.TIMESTAMP(timezone=True), server_default=sa.text(u'now()'), nullable=True))

    sa.Column('p', sa.TIMESTAMP(timezone=True), server_default=sa.text(u"TIMEZONE('utc', CURRENT_TIMESTAMP)"), nullable=True)