Re: pg_restore casts check constraints differently - Mailing list pgsql-general

Joshua Ma <josh@benchling.com> writes:
> 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.

It's not really different.  What you're seeing is pg_dump (or actually
ruleutils.c) choosing to dump some implicit casts explicitly to ensure
that the expression is parsed the same way next time.  It might be
overly conservative to do so, but we've found that erring in this
direction tends to avoid breakage when the result is loaded into another
server version; it's a bit like the intentional overparenthesization.