Can pg_dump be instructed to create tables with "IF NOT EXISTS"?

Question

As per the title.

I've looked at the man page and it doesn't seem like there is any command-line argument to include the "IF NOT EXISTS" clause in the "CREATE TABLE" statements.

Answer 1

No. The closest that pg_dump comes to this in terms of a built-in option is --if-exists, which only works in conjunction with --clean, meaning it only applies to things like DROP commands.

If you want to add this sort of thing, I think your best bet would be to post-process the dumps (assuming you are dumping to pure SQL and not binary format). To cover all the variety of cases, you would technically need a regex that covers this portion of the CREATE TABLE command grammar:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE
       [ IF NOT EXISTS ] table_name

However a simpler regex will likely cover most cases (for example, I don't think you'd ever be dumping temp tables, pretty much definitionally, so that part can be ignored). Similarly, GLOBAL and LOCAL are deprecated, so those can perhaps also be safely ignored, depending on your data.

pg_dump doc for reference.