Re: Idempotent DDL Updates

On Sat, Aug 28, 2021 at 2:19 AM Miles Elam <miles.elam@productops.com> wrote:
>
> What is the general consensus within the community on idempotent DDL scripts, ie. consistent usage of IF EXISTS/IF
NOTEXISTS/OR REPLACE for SQL init files that get checked into source control? 
> [...]
> The drawbacks I've run across are those areas where the EXISTS/REPLACE constructs aren't implemented like roles
management,domains, constraints, etc. However those cases seem to be handled with only minor increases in complexity
withjudicious use of inline plpgsql. 
>
> In others' opinions, has DDL idempotency been viable for maintenance of PG databases fo you in production?

Note that the IF EXISTS / IF NOT EXISTS are *not* idempotent.  If you
need to write idempotent schema update scripts, you need to query the
catalogs to check if the specific change you want to apply has already
been applied or not.