support rendering of func.XYZ(), func.XYZ(simple column), cast(<expr>), etc. within server defaults, functional indexes

[sqlalchemy-bot]

Migrated issue, originally created by Michael Bayer (@zzzeek)

consolidating #64, #196

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Issue #64 was marked as a duplicate of this issue.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Issue #196 was marked as a duplicate of this issue.

[sqlalchemy-bot]

zvell wrote:

Is there a workaround that can be used while this is open? Perhaps manually changing the migration script?
--autogenerate currently created sa.Column('ts', sa.DateTime(), server_default='now()', nullable=True), which gets translated to ALTER TABLE revision ALTER COLUMN ts SET DEFAULT '2014-04-28 12:22:16.485989'::timestamp without time zone;. Is there anything I can replace server_default='now()' with to force it to render to sql properly? I'm using PostgreSQL 9.1.13 and alembic==0.6.4.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

yes, manually changing the migration script is what you're supposed to do. You might notice the script that's generated even has a comment encouraging this. This is why autogenerate migrations are called "candidate migrations".

[sqlalchemy-bot]

zvell wrote:

Oh, yes of course. I guess I was fishing for a solution for my specific problem.

I found a workaround by issuing
op.execute('ALTER TABLE table ALTER COLUMN ts SET DEFAULT CURRENT_TIMESTAMP::timestamp without time zone') after the automatically generated commands.

[sqlalchemy-bot]

zvell wrote:

The same behaviour applies for other server_defaults (e.g. boolean value=False).

If I manually change the migration script to

op.create_table('product', sa.Column('deleted', sa.Boolean(), nullable=True, server_default=False), ...

which is kind of as per the docs, I get this exception on running upgrade

File "/Users/u/env/lib/python2.7/site-packages/alembic/compat.py", line 58, in load_module_py
mod = imp.load_source(module_id, path, fp)
File "/Users/u/app/migration/env.py", line 95, in
run_migrations_online()
File "/Users/u/app/migration/env.py", line 88, in run_migrations_online
context.run_migrations()
File "", line 7, in run_migrations
File "/Users/u/env/lib/python2.7/site-packages/alembic/environment.py", line 681, in run_migrations
self.get_context().run_migrations(**kw)
File "/Users/u/env/lib/python2.7/site-packages/alembic/migration.py", line 225, in run_migrations
change(**kw)
File "/Users/u/app/migration/versions/45c9f48e46dc_revision_products.py", line 21, in upgrade
sa.Column('deleted', sa.Boolean(), nullable=True, server_default=False),
File "/Users/u/env/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 1055, in init
args.append(DefaultClause(self.server_default))
File "/Users/u/env/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 2114, in init
TextClause), 'arg')
File "/Users/u/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 1022, in assert_arg_type
(name, ' or '.join("'%s'" % a for a in argtype), type(arg)))
sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of type '<type 'basestring'>' or '<class 'sqlalchemy.sql.elements.>ClauseElement'>' or '<class 'sqlalchemy.sql.elements.TextClause'>', got '<type 'bool'>'

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

"False" isn't a SQL expression, which server_default requires. the alembic docs you refer to illustrate text(), which indicates a literal SQL string. Here's the main docs for server_default - it only deals with strings and SQL expression elements. you can send it something like text("false"), sqlalchemy.sql.False_, etc.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the exception message is also pretty specific, it's telling you what types it expects. Guess it's not clear enough what those types are....

[sqlalchemy-bot]

zvell wrote:

You are right. The exception message was specific and clear, but I assumed I could have given server_default a Python type and it would just translate it into sql-specifics (potential feature?).

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

we also seek to include expressions like cast(), see #218

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "support rendering of func.XYZ() func.XYZ(simple co" to "support rendering of func.XYZ() func.XYZ(simple co"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

see zzzeek/alembic#16 which has a lot of this

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

also see #222 which contains some infrastructure to support this

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• changelog for support rendering of func.XYZ(), func.XYZ(simple column), cast(<expr>), etc. within server defaults, functional indexes #197, fixes support rendering of func.XYZ(), func.XYZ(simple column), cast(<expr>), etc. within server defaults, functional indexes #197.

→ 75652fe

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed