Alter ADD Column with postgresql server_default

Issue #111 duplicate
Alan Boudreault
created an issue

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(

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" {}

Comments (8)

  1. Alan Boudreault reporter

    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.

  2. Michael Bayer repo owner

    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.

  3. Michael Bayer repo owner

    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)
  4. Log in to comment