[Sqlalchemy-tickets] Issue #3157: Invalid default value for Array (zzzeek/sqlalchemy)

[yedpodtrzitko <iss...@bi...>]

New issue 3157: Invalid default value for Array
https://bitbucket.org/zzzeek/sqlalchemy/issue/3157/invalid-default-value-for-array

yedpodtrzitko:

I'm not sure if I should report it here or in Alembic bugtracker, but as Alembic uses SqlAlchemy for generating migrations content, I'll try it here:

I've a following column in DB: 


```
\d+ "objects._external_txns";
#!bash

     Column     |            Type             |           Modifiers           | Storage  | Stats target | Description
----------------+-----------------------------+-------------------------------+----------+--------------+-------------
 product_ids    | text[]                      | not null default '{}'::text[] | extended |              |

```


when I try to use Alembic for generating a migration the result looks like this:


```
#!python

sa.Column('product_ids', postgresql.ARRAY(TEXT()), server_default="{}'::text[]", autoincrement=False, nullable=False)
```

When I try to run given migration, I get following error:

```
#!python

INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 148fa0264d4, empty message
'{}'::text[]'
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "shell.py", line 28, in <module>
    ctx = domain_mgr.ensure_domain(DOMAIN)
  File "fireline/domains.py", line 263, in ensure_domain
    flush_redis=flush_redis)
  File "fireline/domains.py", line 226, in initialize_domain
    objects.update_system_objects(domain_db_ctx)
  File "fireline/objects.py", line 3198, in update_system_objects
    upgrade(_get_config(), 'head')
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/command.py", line 125, in upgrade
    script.run_env()
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/script.py", line 203, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/util.py", line 212, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/compat.py", line 58, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "alembic/env.py", line 129, in <module>
    run_migrations_online()
  File "alembic/env.py", line 121, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/environment.py", line 688, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/migration.py", line 258, in run_migrations
    change(**kw)
  File "alembic/versions/148fa0264d4_.py", line 425, in upgrade
    sa.PrimaryKeyConstraint('_id', name=u'objects._external_txns_pkey')
  File "<string>", line 7, in create_table
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/operations.py", line 713, in create_table
    self._table(name, *columns, **kw)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/ddl/impl.py", line 149, in create_table
    self._exec(schema.CreateTable(table))
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/alembic/ddl/impl.py", line 76, in _exec
    conn.execute(construct, *multiparams, **params)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 69, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 783, in _execute_ddl
    compiled
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in _handle_dbapi_exception
    exc_info
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/Users/yed/dev/sd/event-core/venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) unterminated quoted string at or near "' NOT NULL,
	CONSTRAINT "objects._external_txns_pkey" PRIMARY KEY (_id)
)

"
LINE 14:  product_ids TEXT[] DEFAULT '{}'::text[]' NOT NULL,
                                                 ^
 '\nCREATE TABLE "objects._external_txns" (\n\t_id UUID NOT NULL, \n\t_created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL, \n\t_modified_at TIMESTAMP WITHOUT TIME ZONE NOT NULL, \n\tvendor VARCHAR NOT NULL, \n\texternal_id TEXT NOT NULL, \n\tnonce_identity UUID NOT NULL, \n\tidentity UUID NOT NULL, \n\tpayload BYTEA, \n\tcurrency TEXT, \n\tamount NUMERIC, \n\tdata JSON, \n\tproduct_ids TEXT[] DEFAULT \'{}\'::text[]\' NOT NULL, \n\tCONSTRAINT "objects._external_txns_pkey" PRIMARY KEY (_id)\n)\n\n' {}
```


 


SqlAlchemy: 0.9.7
Postgres: 9.3.5
Alembic: 0.6.5