JSONB -> ARRAY ALTER failure

[sqlalchemy-bot]

Migrated issue, originally created by Zsolt Ero (hyperknot)

I am changing a column type from JSONB to ARRAY (in PG 9.6).

I guess it should be dropped and re-created, but maybe it can be altered, I don't know, but it results in an error.

Migration script:

    op.alter_column('images', 'tags',
               existing_type=postgresql.JSONB(astext_type=sa.Text()),
               type_=postgresql.ARRAY(sa.Unicode()),
               nullable=True)

Error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "tags" cannot be cast automatically to type character varying[]
HINT:  You might need to specify "USING tags::character varying[]".
 [SQL: 'ALTER TABLE images ALTER COLUMN tags TYPE VARCHAR[] ']

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

we support postgresql_using:

http://alembic.zzzcomputing.com/en/latest/ops.html?highlight=alter_column#alembic.operations.Operations.alter_column.params.postgresql_using

why not just add what the message says? postgresql_using="tags::character varying[]". This message is coming from your database. please close this assuming it works, thanks.

[sqlalchemy-bot]

Zsolt Ero (hyperknot) wrote:

Sorry, the point I tried to raise was that the automatic script generation results in an error.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

That's never going to be automatic you need to make adjustments like that manually​.

[sqlalchemy-bot]

Zsolt Ero (hyperknot) wrote:

I see, then feel free to close it.

[sqlalchemy-bot]

Changes by Zsolt Ero (hyperknot):

• edited description