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.

Papain

There isn't. If you want to do that, wrap it in a DO block that checks information_schema to see if the table exists. The reason why is that IF NOT EXISTS has no way to handle the case where a completely different table with the same name exists. – Liquidize

This is no good reason imo. if there is a table with completely different columns then it should be named differently. So imo an IF NOT EXISTS would make sense – Implode

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.

Schell