*** a/doc/src/sgml/ref/pg_dump.sgml --- b/doc/src/sgml/ref/pg_dump.sgml *************** *** 592,597 **** PostgreSQL documentation --- 592,607 ---- + + + + It use conditional commands (with IF EXISTS + clause) for cleaning database schema. + + + + + *** a/doc/src/sgml/ref/pg_dumpall.sgml --- b/doc/src/sgml/ref/pg_dumpall.sgml *************** *** 270,275 **** PostgreSQL documentation --- 270,285 ---- + + + + It use conditional commands (with IF EXISTS + clause) for cleaning database schema. + + + + + *** a/src/bin/pg_dump/pg_backup.h --- b/src/bin/pg_dump/pg_backup.h *************** *** 108,113 **** typedef struct _restoreOptions --- 108,114 ---- char *superuser; /* Username to use as superuser */ char *use_role; /* Issue SET ROLE to this */ int dropSchema; + int if_exists; const char *filename; int dataOnly; int schemaOnly; *** a/src/bin/pg_dump/pg_backup_archiver.c --- b/src/bin/pg_dump/pg_backup_archiver.c *************** *** 118,125 **** static const char *modulename = gettext_noop("archiver"); static ArchiveHandle *_allocAH(const char *FileSpec, const ArchiveFormat fmt, const int compression, ArchiveMode mode); static void _getObjectDescription(PQExpBuffer buf, TocEntry *te, ! ArchiveHandle *AH); ! static void _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isData, bool acl_pass); static char *replace_line_endings(const char *str); static void _doSetFixedOutputState(ArchiveHandle *AH); static void _doSetSessionAuth(ArchiveHandle *AH, const char *user); --- 118,126 ---- static ArchiveHandle *_allocAH(const char *FileSpec, const ArchiveFormat fmt, const int compression, ArchiveMode mode); static void _getObjectDescription(PQExpBuffer buf, TocEntry *te, ! ArchiveHandle *AH, bool if_exists); ! static void _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, ! bool isData, bool acl_pass); static char *replace_line_endings(const char *str); static void _doSetFixedOutputState(ArchiveHandle *AH); static void _doSetSessionAuth(ArchiveHandle *AH, const char *user); *************** *** 296,301 **** RestoreArchive(Archive *AHX) --- 297,303 ---- bool parallel_mode; TocEntry *te; OutputContext sav; + AH->stage = STAGE_INITIALIZING; *************** *** 2903,2909 **** _selectTablespace(ArchiveHandle *AH, const char *tablespace) * information used is all that's available in older dump files. */ static void ! _getObjectDescription(PQExpBuffer buf, TocEntry *te, ArchiveHandle *AH) { const char *type = te->desc; --- 2905,2911 ---- * information used is all that's available in older dump files. */ static void ! _getObjectDescription(PQExpBuffer buf, TocEntry *te, ArchiveHandle *AH, bool if_exists) { const char *type = te->desc; *************** *** 2969,2977 **** _getObjectDescription(PQExpBuffer buf, TocEntry *te, ArchiveHandle *AH) strcmp(type, "OPERATOR CLASS") == 0 || strcmp(type, "OPERATOR FAMILY") == 0) { ! /* Chop "DROP " off the front and make a modifiable copy */ ! char *first = pg_strdup(te->dropStmt + 5); ! char *last; /* point to last character in string */ last = first + strlen(first) - 1; --- 2971,3006 ---- strcmp(type, "OPERATOR CLASS") == 0 || strcmp(type, "OPERATOR FAMILY") == 0) { ! char *first; ! char *last; ! ! if (!if_exists) ! { ! /* just chop first 5 chars - "DROP", and create a modifiable copy */ ! first = pg_strdup(te->dropStmt + 5); ! } ! else ! { ! char buffer[40]; ! size_t l; ! ! /* IF EXISTS clause should be optional, check it*/ ! snprintf(buffer, sizeof(buffer), "DROP %s%s", type, ! if_exists ? " IF EXISTS" : ""); ! l = strlen(buffer); ! ! if (strncmp(te->dropStmt, buffer, l) == 0) ! { ! /* append command type to target type */ ! appendPQExpBufferStr(buf, type); ! ! /* skip first n chars, and create a modifieble copy */ ! first = pg_strdup(te->dropStmt + l); ! } ! else ! /* IF EXISTS clause was not used, simple solution */ ! first = pg_strdup(te->dropStmt + 5); ! } /* point to last character in string */ last = first + strlen(first) - 1; *************** *** 2992,2999 **** _getObjectDescription(PQExpBuffer buf, TocEntry *te, ArchiveHandle *AH) } static void ! _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isData, bool acl_pass) { /* ACLs are dumped only during acl pass */ if (acl_pass) { --- 3021,3031 ---- } static void ! _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isData, ! bool acl_pass) { + int if_exists = ropt->if_exists; + /* ACLs are dumped only during acl pass */ if (acl_pass) { *************** *** 3150,3156 **** _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat PQExpBuffer temp = createPQExpBuffer(); appendPQExpBuffer(temp, "ALTER "); ! _getObjectDescription(temp, te, AH); appendPQExpBuffer(temp, " OWNER TO %s;", fmtId(te->owner)); ahprintf(AH, "%s\n\n", temp->data); destroyPQExpBuffer(temp); --- 3182,3188 ---- PQExpBuffer temp = createPQExpBuffer(); appendPQExpBuffer(temp, "ALTER "); ! _getObjectDescription(temp, te, AH, if_exists); appendPQExpBuffer(temp, " OWNER TO %s;", fmtId(te->owner)); ahprintf(AH, "%s\n\n", temp->data); destroyPQExpBuffer(temp); *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *************** *** 137,142 **** static int column_inserts = 0; --- 137,143 ---- static int no_security_labels = 0; static int no_unlogged_table_data = 0; static int serializable_deferrable = 0; + static int if_exists = 0; static void help(const char *progname); *************** *** 338,343 **** main(int argc, char **argv) --- 339,345 ---- {"attribute-inserts", no_argument, &column_inserts, 1}, {"binary-upgrade", no_argument, &binary_upgrade, 1}, {"column-inserts", no_argument, &column_inserts, 1}, + {"if-exists", no_argument, &if_exists, 1}, {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1}, {"disable-triggers", no_argument, &disable_triggers, 1}, {"exclude-table-data", required_argument, NULL, 4}, *************** *** 551,556 **** main(int argc, char **argv) --- 553,561 ---- exit_nicely(1); } + if (if_exists && !outputClean) + exit_horribly(NULL, "option --if-exists requires -c/--clean option\n"); + /* Identify archive format to emit */ archiveFormat = parseArchiveFormat(format, &archiveMode); *************** *** 780,785 **** main(int argc, char **argv) --- 785,791 ---- ropt->dropSchema = outputClean; ropt->dataOnly = dataOnly; ropt->schemaOnly = schemaOnly; + ropt->if_exists = if_exists; ropt->dumpSections = dumpSections; ropt->aclsSkip = aclsSkip; ropt->superuser = outputSuperuser; *************** *** 857,862 **** help(const char *progname) --- 863,869 ---- printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); printf(_(" --binary-upgrade for use by upgrade utilities only\n")); printf(_(" --column-inserts dump data as INSERT commands with column names\n")); + printf(_(" --if-exists don't report error if cleaned object doesn't exist\n")); printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n")); printf(_(" --disable-triggers disable triggers during data-only restore\n")); printf(_(" --exclude-table-data=TABLE do NOT dump data for the named table(s)\n")); *************** *** 1987,1993 **** dumpDatabase(Archive *fout) } ! appendPQExpBuffer(delQry, "DROP DATABASE %s;\n", fmtId(datname)); dbDumpId = createDumpId(); --- 1994,2001 ---- } ! appendPQExpBuffer(delQry, "DROP DATABASE %s%s;\n", ! if_exists ? "IF EXISTS " : "", fmtId(datname)); dbDumpId = createDumpId(); *************** *** 7431,7437 **** dumpNamespace(Archive *fout, NamespaceInfo *nspinfo) qnspname = pg_strdup(fmtId(nspinfo->dobj.name)); ! appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname); appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname); --- 7439,7447 ---- qnspname = pg_strdup(fmtId(nspinfo->dobj.name)); ! appendPQExpBuffer(delq, "DROP SCHEMA %s%s;\n", ! if_exists ? "IF EXISTS " : "", ! qnspname); appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname); *************** *** 7490,7496 **** dumpExtension(Archive *fout, ExtensionInfo *extinfo) qextname = pg_strdup(fmtId(extinfo->dobj.name)); ! appendPQExpBuffer(delq, "DROP EXTENSION %s;\n", qextname); if (!binary_upgrade) { --- 7500,7508 ---- qextname = pg_strdup(fmtId(extinfo->dobj.name)); ! appendPQExpBuffer(delq, "DROP EXTENSION %s%s;\n", ! if_exists ? "IF EXISTS " : "", ! qextname); if (!binary_upgrade) { *************** *** 7664,7670 **** dumpEnumType(Archive *fout, TypeInfo *tyinfo) * CASCADE shouldn't be required here as for normal types since the I/O * functions are generic and do not get dropped. */ ! appendPQExpBuffer(delq, "DROP TYPE %s.", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", qtypname); --- 7676,7683 ---- * CASCADE shouldn't be required here as for normal types since the I/O * functions are generic and do not get dropped. */ ! appendPQExpBuffer(delq, "DROP TYPE %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", qtypname); *************** *** 7794,7800 **** dumpRangeType(Archive *fout, TypeInfo *tyinfo) * CASCADE shouldn't be required here as for normal types since the I/O * functions are generic and do not get dropped. */ ! appendPQExpBuffer(delq, "DROP TYPE %s.", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", qtypname); --- 7807,7814 ---- * CASCADE shouldn't be required here as for normal types since the I/O * functions are generic and do not get dropped. */ ! appendPQExpBuffer(delq, "DROP TYPE %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", qtypname); *************** *** 8134,8140 **** dumpBaseType(Archive *fout, TypeInfo *tyinfo) * the type and its I/O functions makes it impossible to drop the type any * other way. */ ! appendPQExpBuffer(delq, "DROP TYPE %s.", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s CASCADE;\n", qtypname); --- 8148,8155 ---- * the type and its I/O functions makes it impossible to drop the type any * other way. */ ! appendPQExpBuffer(delq, "DROP TYPE %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s CASCADE;\n", qtypname); *************** *** 8394,8400 **** dumpDomain(Archive *fout, TypeInfo *tyinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP DOMAIN %s.", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", qtypname); --- 8409,8416 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP DOMAIN %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", qtypname); *************** *** 8609,8615 **** dumpCompositeType(Archive *fout, TypeInfo *tyinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TYPE %s.", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", qtypname); --- 8625,8632 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TYPE %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tyinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", qtypname); *************** *** 8920,8926 **** dumpProcLang(Archive *fout, ProcLangInfo *plang) else lanschema = NULL; ! appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s;\n", qlanname); if (useParams) --- 8937,8944 ---- else lanschema = NULL; ! appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s%s;\n", ! if_exists ? "IF EXISTS " : "", qlanname); if (useParams) *************** *** 9461,9467 **** dumpFunc(Archive *fout, FuncInfo *finfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n", fmtId(finfo->dobj.namespace->dobj.name), funcsig); --- 9479,9486 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delqry, "DROP FUNCTION %s%s.%s;\n", ! if_exists ? "IF EXISTS " : "", fmtId(finfo->dobj.namespace->dobj.name), funcsig); *************** *** 9679,9685 **** dumpCast(Archive *fout, CastInfo *cast) delqry = createPQExpBuffer(); labelq = createPQExpBuffer(); ! appendPQExpBuffer(delqry, "DROP CAST (%s AS %s);\n", getFormattedTypeName(fout, cast->castsource, zeroAsNone), getFormattedTypeName(fout, cast->casttarget, zeroAsNone)); --- 9698,9705 ---- delqry = createPQExpBuffer(); labelq = createPQExpBuffer(); ! appendPQExpBuffer(delqry, "DROP CAST %s(%s AS %s);\n", ! if_exists ? "IF EXISTS " : "", getFormattedTypeName(fout, cast->castsource, zeroAsNone), getFormattedTypeName(fout, cast->casttarget, zeroAsNone)); *************** *** 9949,9955 **** dumpOpr(Archive *fout, OprInfo *oprinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP OPERATOR %s.%s;\n", fmtId(oprinfo->dobj.namespace->dobj.name), oprid->data); --- 9969,9976 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP OPERATOR %s%s.%s;\n", ! if_exists ? "IF EXISTS " : "", fmtId(oprinfo->dobj.namespace->dobj.name), oprid->data); *************** *** 10235,10241 **** dumpOpclass(Archive *fout, OpclassInfo *opcinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s", fmtId(opcinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s", fmtId(opcinfo->dobj.name)); --- 10256,10263 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s%s", ! if_exists ? "IF EXISTS " : "", fmtId(opcinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s", fmtId(opcinfo->dobj.name)); *************** *** 10679,10685 **** dumpOpfamily(Archive *fout, OpfamilyInfo *opfinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP OPERATOR FAMILY %s", fmtId(opfinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s", fmtId(opfinfo->dobj.name)); --- 10701,10708 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP OPERATOR FAMILY %s%s", ! if_exists ? "IF EXISTS " : "", fmtId(opfinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s", fmtId(opfinfo->dobj.name)); *************** *** 10853,10859 **** dumpCollation(Archive *fout, CollInfo *collinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP COLLATION %s", fmtId(collinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(collinfo->dobj.name)); --- 10876,10883 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP COLLATION %s%s", ! if_exists ? "IF EXISTS " : "", fmtId(collinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(collinfo->dobj.name)); *************** *** 10950,10956 **** dumpConversion(Archive *fout, ConvInfo *convinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP CONVERSION %s", fmtId(convinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(convinfo->dobj.name)); --- 10974,10981 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP CONVERSION %s%s", ! if_exists ? "IF EXISTS " : "", fmtId(convinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(convinfo->dobj.name)); *************** *** 11194,11200 **** dumpAgg(Archive *fout, AggInfo *agginfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n", fmtId(agginfo->aggfn.dobj.namespace->dobj.name), aggsig); --- 11219,11226 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP AGGREGATE %s%s.%s;\n", ! if_exists ? "IF EXISTS " : "", fmtId(agginfo->aggfn.dobj.namespace->dobj.name), aggsig); *************** *** 11293,11299 **** dumpTSParser(Archive *fout, TSParserInfo *prsinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TEXT SEARCH PARSER %s", fmtId(prsinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(prsinfo->dobj.name)); --- 11319,11326 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TEXT SEARCH PARSER %s%s", ! if_exists ? "IF EXISTS " : "", fmtId(prsinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(prsinfo->dobj.name)); *************** *** 11380,11386 **** dumpTSDictionary(Archive *fout, TSDictInfo *dictinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TEXT SEARCH DICTIONARY %s", fmtId(dictinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(dictinfo->dobj.name)); --- 11407,11414 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TEXT SEARCH DICTIONARY %s%s", ! if_exists ? "IF EXISTS " : "", fmtId(dictinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(dictinfo->dobj.name)); *************** *** 11446,11452 **** dumpTSTemplate(Archive *fout, TSTemplateInfo *tmplinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TEXT SEARCH TEMPLATE %s", fmtId(tmplinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(tmplinfo->dobj.name)); --- 11474,11481 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TEXT SEARCH TEMPLATE %s%s", ! if_exists ? "IF EXISTS " : "", fmtId(tmplinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(tmplinfo->dobj.name)); *************** *** 11574,11580 **** dumpTSConfig(Archive *fout, TSConfigInfo *cfginfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TEXT SEARCH CONFIGURATION %s", fmtId(cfginfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(cfginfo->dobj.name)); --- 11603,11610 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "DROP TEXT SEARCH CONFIGURATION %s%s", ! if_exists ? "IF EXISTS " : "", fmtId(cfginfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, ".%s;\n", fmtId(cfginfo->dobj.name)); *************** *** 11650,11656 **** dumpForeignDataWrapper(Archive *fout, FdwInfo *fdwinfo) appendPQExpBuffer(q, ";\n"); ! appendPQExpBuffer(delq, "DROP FOREIGN DATA WRAPPER %s;\n", qfdwname); appendPQExpBuffer(labelq, "FOREIGN DATA WRAPPER %s", --- 11680,11687 ---- appendPQExpBuffer(q, ";\n"); ! appendPQExpBuffer(delq, "DROP FOREIGN DATA WRAPPER %s%s;\n", ! if_exists ? "IF EXISTS " : "", qfdwname); appendPQExpBuffer(labelq, "FOREIGN DATA WRAPPER %s", *************** *** 11743,11749 **** dumpForeignServer(Archive *fout, ForeignServerInfo *srvinfo) appendPQExpBuffer(q, ";\n"); ! appendPQExpBuffer(delq, "DROP SERVER %s;\n", qsrvname); appendPQExpBuffer(labelq, "SERVER %s", qsrvname); --- 11774,11781 ---- appendPQExpBuffer(q, ";\n"); ! appendPQExpBuffer(delq, "DROP SERVER %s%s;\n", ! if_exists ? "IF EXISTS " : "", qsrvname); appendPQExpBuffer(labelq, "SERVER %s", qsrvname); *************** *** 11861,11867 **** dumpUserMappings(Archive *fout, appendPQExpBuffer(q, ";\n"); resetPQExpBuffer(delq); ! appendPQExpBuffer(delq, "DROP USER MAPPING FOR %s", fmtId(usename)); appendPQExpBuffer(delq, " SERVER %s;\n", fmtId(servername)); resetPQExpBuffer(tag); --- 11893,11901 ---- appendPQExpBuffer(q, ";\n"); resetPQExpBuffer(delq); ! appendPQExpBuffer(delq, "DROP USER MAPPING %sFOR %s", ! if_exists ? "IF EXISTS " : "", ! fmtId(usename)); appendPQExpBuffer(delq, " SERVER %s;\n", fmtId(servername)); resetPQExpBuffer(tag); *************** *** 12448,12454 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "DROP VIEW %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", fmtId(tbinfo->dobj.name)); --- 12482,12489 ---- * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "DROP VIEW %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", fmtId(tbinfo->dobj.name)); *************** *** 12510,12516 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo) * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "DROP %s %s.", reltypename, fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", fmtId(tbinfo->dobj.name)); --- 12545,12552 ---- * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "DROP %s %s%s.", reltypename, ! if_exists ? "IF EXISTS " : "", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", fmtId(tbinfo->dobj.name)); *************** *** 13002,13008 **** dumpAttrDef(Archive *fout, AttrDefInfo *adinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "ALTER TABLE %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s ", fmtId(tbinfo->dobj.name)); --- 13038,13045 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delq, "ALTER TABLE %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s ", fmtId(tbinfo->dobj.name)); *************** *** 13106,13112 **** dumpIndex(Archive *fout, IndxInfo *indxinfo) * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "DROP INDEX %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", fmtId(indxinfo->dobj.name)); --- 13143,13150 ---- * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "DROP INDEX %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s;\n", fmtId(indxinfo->dobj.name)); *************** *** 13225,13231 **** dumpConstraint(Archive *fout, ConstraintInfo *coninfo) * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s ", fmtId(tbinfo->dobj.name)); --- 13263,13270 ---- * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "ALTER TABLE %sONLY %s.", ! if_exists ? "IF EXISTS " : "", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s ", fmtId(tbinfo->dobj.name)); *************** *** 13258,13264 **** dumpConstraint(Archive *fout, ConstraintInfo *coninfo) * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s ", fmtId(tbinfo->dobj.name)); --- 13297,13304 ---- * DROP must be fully qualified in case same name appears in * pg_catalog */ ! appendPQExpBuffer(delq, "ALTER TABLE %sONLY %s.", ! if_exists ? "IF EXISTS " : "", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delq, "%s ", fmtId(tbinfo->dobj.name)); *************** *** 13527,13533 **** dumpSequence(Archive *fout, TableInfo *tbinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delqry, "DROP SEQUENCE %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delqry, "%s;\n", fmtId(tbinfo->dobj.name)); --- 13567,13574 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delqry, "DROP SEQUENCE %s%s.", ! if_exists ? "IF EXISTS " : "", fmtId(tbinfo->dobj.namespace->dobj.name)); appendPQExpBuffer(delqry, "%s;\n", fmtId(tbinfo->dobj.name)); *************** *** 13715,13721 **** dumpTrigger(Archive *fout, TriggerInfo *tginfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delqry, "DROP TRIGGER %s ", fmtId(tginfo->dobj.name)); appendPQExpBuffer(delqry, "ON %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); --- 13756,13763 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delqry, "DROP TRIGGER %s%s ", ! if_exists ? "IF EXISTS " : "", fmtId(tginfo->dobj.name)); appendPQExpBuffer(delqry, "ON %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); *************** *** 14055,14061 **** dumpRule(Archive *fout, RuleInfo *rinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delcmd, "DROP RULE %s ", fmtId(rinfo->dobj.name)); appendPQExpBuffer(delcmd, "ON %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); --- 14097,14104 ---- /* * DROP must be fully qualified in case same name appears in pg_catalog */ ! appendPQExpBuffer(delcmd, "DROP RULE %s%s ", ! if_exists ? "IF EXISTS " : "", fmtId(rinfo->dobj.name)); appendPQExpBuffer(delcmd, "ON %s.", fmtId(tbinfo->dobj.namespace->dobj.name)); *** a/src/bin/pg_dump/pg_dumpall.c --- b/src/bin/pg_dump/pg_dumpall.c *************** *** 68,73 **** static bool verbose = false; --- 68,74 ---- static int binary_upgrade = 0; static int column_inserts = 0; + static int if_exists = 0; static int disable_dollar_quoting = 0; static int disable_triggers = 0; static int inserts = 0; *************** *** 112,117 **** main(int argc, char *argv[]) --- 113,119 ---- {"attribute-inserts", no_argument, &column_inserts, 1}, {"binary-upgrade", no_argument, &binary_upgrade, 1}, {"column-inserts", no_argument, &column_inserts, 1}, + {"if-exists", no_argument, &if_exists, 1}, {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1}, {"disable-triggers", no_argument, &disable_triggers, 1}, {"inserts", no_argument, &inserts, 1}, *************** *** 345,350 **** main(int argc, char *argv[]) --- 347,354 ---- appendPQExpBuffer(pgdumpopts, " --binary-upgrade"); if (column_inserts) appendPQExpBuffer(pgdumpopts, " --column-inserts"); + if (if_exists) + appendPQExpBuffer(pgdumpopts, " --if-exists"); if (disable_dollar_quoting) appendPQExpBuffer(pgdumpopts, " --disable-dollar-quoting"); if (disable_triggers) *************** *** 556,561 **** help(void) --- 560,566 ---- printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); printf(_(" --binary-upgrade for use by upgrade utilities only\n")); printf(_(" --column-inserts dump data as INSERT commands with column names\n")); + printf(_(" --if-exists don't report error if cleaned object doesn't exist\n")); printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n")); printf(_(" --disable-triggers disable triggers during data-only restore\n")); printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));