On Sat, Aug 28, 2021 at 2:19 AM Miles Elam <miles.e...@productops.com> wrote: > > What is the general consensus within the community on idempotent DDL scripts, > ie. consistent usage of IF EXISTS/IF NOT EXISTS/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 with judicious 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.
