replace_entity with a SQL view does not allow insertion or re-ordering of columns in PostGres

[Woodz]

Reproducible

1. Create my_view.py:

first_view = PGView(
    schema="public",
    signature="first_view",
    definition="select 'Test' as "Foo" from information_schema.tables",
)

2. Create migration and run
3. Modify my_view.py:

first_view = PGView(
    schema="public",
    signature="first_view",
    definition="select 1 as "Bar", 'Test' as "Foo" from information_schema.tables",
)

4. Create migration and run

Actual

Error

cannot change name of view column "Foo" to "Bar"
[SQL: CREATE OR REPLACE VIEW "public"

Expected

Success

Suggestion

It looks like this is caused by using SQL command CREATE OR REPLACE VIEW, which is known to only support appending columns, not inserting or re-ordering

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

https://www.postgresql.org/docs/9.3/sql-createview.html

As such, it seems as though the view should be dropped and recreated

[olirice]

Dupe of #5

There is a snippet you can use to get that behavior in the comments

[Woodz]

Apologies! I forgot to check closed issues for dupes

[olirice]

No worries

3 out of 20 issues have been requesting this feature so I think its worth updating the default behavior!

release 0.2.12 is now live on pypi and fails over from create or replace view to drop view ...; create view .... by default