This might not be a common case, but we're using pg_dump in a testing environment to check migrations - 1) we initialize the db from HEAD, pg_dump it, 2) we initialize the db from migration_base.sql, apply migrations, pg_dump it, and 3) compare the two dumps to verify that our migrations are correct wrt schema.
--
However, we're seeing pg_restore transforming our check constraints with different casting.
# \d arrayed_library
CONSTRAINT arrayed_library_step_check CHECK (((step)::text = ANY ((ARRAY['ADD_RESERVED_SEQUENCES'::character varying, 'ANALYZE_DESIGN_WARNINGS'::character varying, 'COMPLETE_ORDER'::character varying, 'DEFINE_VARIANTS'::character varying, 'LABEL_TRANSLATION'::character varying])::text[])))
$ dropdb db && createdb db
$ pg_dump db --schema-only --no-owner > migration_base.sql
# migration_base.sql has the same CONSTRAINT as above
$ psql db -q -f migration_base.sql
# \d arrayed_library
CONSTRAINT arrayed_library_step_check CHECK (((step)::text = ANY (ARRAY[('ADD_RESERVED_SEQUENCES'::character varying)::text, ('ANALYZE_DESIGN_WARNINGS'::character varying)::text, ('COMPLETE_ORDER'::character varying)::text, ('DEFINE_VARIANTS'::character varying)::text, ('LABEL_TRANSLATION'::character varying)::text])))
Note that the restored constraint has ARRAY('a'::text, 'b'::text, ...) while the original had (ARRAY['a', 'b', ...])::text[]
Is there any way to have postgres NOT do the extra conversions?
- Josh
