diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 50809b4844..a36fd20328 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -894,6 +894,17 @@ PostgreSQL documentation
+
+
+
+
+ Add ON CONFLICT DO NOTHING clause in the INSERT commands.
+ This option is not valid unless or
+ is also specified.
+
+
+
+
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 5d6fe9b87d..ff710e7568 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -431,6 +431,17 @@ PostgreSQL documentation
+
+
+
+
+ Add ON CONFLICT DO NOTHING clause in the INSERT commands.
+ This option is not valid unless or
+ is also specified.
+
+
+
+
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index ceedd481fb..42cf441aaf 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -172,6 +172,7 @@ typedef struct _dumpOptions
char *outputSuperuser;
int sequence_data; /* dump sequence data even in schema-only mode */
+ int do_nothing;
} DumpOptions;
/*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d6ceb72c05..facece2e6f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -378,6 +378,7 @@ main(int argc, char **argv)
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
{"no-subscriptions", no_argument, &dopt.no_subscriptions, 1},
{"no-sync", no_argument, NULL, 7},
+ {"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
{NULL, 0, NULL, 0}
};
@@ -619,6 +620,9 @@ main(int argc, char **argv)
if (dopt.if_exists && !dopt.outputClean)
exit_horribly(NULL, "option --if-exists requires option -c/--clean\n");
+ if (dopt.do_nothing && !(dopt.dump_inserts || dopt.column_inserts))
+ exit_horribly(NULL, "option --on-conflict-do-nothing requires option --inserts or --column-inserts\n");
+
/* Identify archive format to emit */
archiveFormat = parseArchiveFormat(format, &archiveMode);
@@ -988,6 +992,7 @@ help(const char *progname)
printf(_(" --no-synchronized-snapshots do not use synchronized snapshots in parallel jobs\n"));
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
+ printf(_(" --on-conflict-do-nothing dump data as INSERT commands with ON CONFLICT DO NOTHING \n"));
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
printf(_(" --section=SECTION dump named section (pre-data, data, or post-data)\n"));
@@ -2047,7 +2052,11 @@ dumpTableData_insert(Archive *fout, void *dcontext)
break;
}
}
- archputs(");\n", fout);
+
+ if (!dopt->do_nothing)
+ archputs(");\n", fout);
+ else
+ archputs(") ON CONFLICT DO NOTHING;\n", fout);
}
if (PQntuples(res) <= 0)
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 55de7449b4..6574ac479c 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -78,6 +78,7 @@ static int no_unlogged_table_data = 0;
static int no_role_passwords = 0;
static int server_version;
static int load_via_partition_root = 0;
+static int on_conflict_do_nothing = 0;
static char role_catalog[10];
#define PG_AUTHID "pg_authid"
@@ -137,6 +138,7 @@ main(int argc, char *argv[])
{"no-subscriptions", no_argument, &no_subscriptions, 1},
{"no-sync", no_argument, NULL, 4},
{"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1},
+ {"on-conflict-do-nothing", no_argument, &on_conflict_do_nothing, 1},
{NULL, 0, NULL, 0}
};
@@ -367,6 +369,12 @@ main(int argc, char *argv[])
exit_nicely(1);
}
+ if (on_conflict_do_nothing && !(inserts || column_inserts))
+ {
+ fprintf(stderr, _("%s: option --on-conflict-do-nothing requires option --inserts or --column-inserts\n"),
+ progname);
+ exit_nicely(1);
+ }
/*
* If password values are not required in the dump, switch to using
* pg_roles which is equally useful, just more likely to have unrestricted
@@ -390,6 +398,8 @@ main(int argc, char *argv[])
appendPQExpBufferStr(pgdumpopts, " --inserts");
if (no_tablespaces)
appendPQExpBufferStr(pgdumpopts, " --no-tablespaces");
+ if (on_conflict_do_nothing)
+ appendPQExpBufferStr(pgdumpopts, " --on-conflict-do-nothing");
if (quote_all_identifiers)
appendPQExpBufferStr(pgdumpopts, " --quote-all-identifiers");
if (load_via_partition_root)
@@ -620,6 +630,7 @@ help(void)
printf(_(" --no-sync do not wait for changes to be written safely to disk\n"));
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
+ printf(_(" --on-conflict-do-nothing dump data as INSERT commands with ON CONFLICT DO NOTHING \n"));
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
printf(_(" --use-set-session-authorization\n"
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
index 8be5770ba4..8eaf556df6 100644
--- a/src/bin/pg_dump/t/001_basic.pl
+++ b/src/bin/pg_dump/t/001_basic.pl
@@ -4,7 +4,7 @@ use warnings;
use Config;
use PostgresNode;
use TestLib;
-use Test::More tests => 68;
+use Test::More tests => 72;
my $tempdir = TestLib::tempdir;
my $tempdir_short = TestLib::tempdir_short;
@@ -122,6 +122,11 @@ command_fails_like(
qr/\Qpg_restore: unrecognized archive format "garbage";\E/,
'pg_dump: unrecognized archive format');
+command_fails_like(
+ [ 'pg_dump', '--on-conflict-do-nothing' ],
+ qr/\Qpg_dump: option --on-conflict-do-nothing requires option --inserts or --column-inserts\E/,
+ 'pg_dump: option --on-conflict-do-nothing requires option --inserts or --column-inserts');
+
# pg_dumpall command-line argument checks
command_fails_like(
[ 'pg_dumpall', '-g', '-r' ],
@@ -145,3 +150,9 @@ command_fails_like(
[ 'pg_dumpall', '--if-exists' ],
qr/\Qpg_dumpall: option --if-exists requires option -c\/--clean\E/,
'pg_dumpall: option --if-exists requires option -c/--clean');
+
+command_fails_like(
+ [ 'pg_dumpall', '--on-conflict-do-nothing' ],
+ qr/\Qpg_dumpall: option --on-conflict-do-nothing requires option --inserts or --column-inserts\E/,
+ 'pg_dumpall: option --on-conflict-do-nothing requires option --inserts or --column-inserts'
+);