[kemitche]

The pitfall is pretty simply stated: "It masks the problem with logic, adding some risks of anomalies." When one puts a bandaid over bad data, the problem isn't solved, it's masked. Depending on the issue, it could bite you later, in unexpected ways, at the worst possible times.

In this particular case, the "bad data" is a table/column/view that exists (or doesn't) when it should(n't). Why does the table exist when it shouldn't yet exist? Did it fail to get dropped? Is the existing one the right schema? Is the same migration erroneously running twice?

After each migration, your schema should be in a precise state. If the phrase "IF [NOT] EXISTS" is in your migration, then after a previous migration, it wasn't left in a precise state. Being uncertain about the state of your schema isn't great.

[kroolik]

We use IF NOT EXISTS to bring non prod environments in sync with prod. The size of prod requires some migrations to be done separately over course of days, in separate transactions. The IF NOT EXISTS clause then brings dev and non prod envs in sync.

[hot_gril]

In many cases you can still be ok just using IF NOT EXISTS everywhere, or occasionally IF EXISTS when trying to delete a no-longer-used col or index. Whether you start with a blank DB or any intermediate state, you'd end up with the same thing. But it gets messy after a long enough time.