Skip to content

Commit 387a5cf

Browse files
macdicemacdice
committed
Add pg_dump --on-conflict-do-nothing option.
When dumping INSERT statements, optionally add ON CONFLICT DO NOTHING. Author: Surafel Temesgen Reviewed-by: Takeshi Ideriha, Nico Williams, Dilip Kumar Discussion: https://postgr.es/m/CALAY4q-PQ9cOEzs2%2BQHK5ObfF_4QbmBaYXbZx6BGGN66Q-n8FA%40mail.gmail.com
1 parent ce89ad0 commit 387a5cf

File tree

6 files changed

+46
-2
lines changed

6 files changed

+46
-2
lines changed

‎doc/src/sgml/ref/pg_dump.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -909,6 +909,18 @@ PostgreSQL documentation
909909
</listitem>
910910
</varlistentry>
911911

912+
<varlistentry>
913+
<term><option>--on-conflict-do-nothing</option></term>
914+
<listitem>
915+
<para>
916+
Add <literal>ON CONFLICT DO NOTHING</literal> to
917+
<command>INSERT</command> commands.
918+
This option is not valid unless <option>--inserts</option> or
919+
<option>--column-inserts</option> is also specified.
920+
</para>
921+
</listitem>
922+
</varlistentry>
923+
912924
<varlistentry>
913925
<term><option>--quote-all-identifiers</option></term>
914926
<listitem>

‎doc/src/sgml/ref/pg_dumpall.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -446,6 +446,18 @@ PostgreSQL documentation
446446
</listitem>
447447
</varlistentry>
448448

449+
<varlistentry>
450+
<term><option>--on-conflict-do-nothing</option></term>
451+
<listitem>
452+
<para>
453+
Add <literal>ON CONFLICT DO NOTHING</literal> to
454+
<command>INSERT</command> commands.
455+
This option is not valid unless <option>--inserts</option> or
456+
<option>--column-inserts</option> is also specified.
457+
</para>
458+
</listitem>
459+
</varlistentry>
460+
449461
<varlistentry>
450462
<term><option>--quote-all-identifiers</option></term>
451463
<listitem>

‎src/bin/pg_dump/pg_backup.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -172,6 +172,7 @@ typedef struct _dumpOptions
172172
char *outputSuperuser;
173173

174174
int sequence_data; /* dump sequence data even in schema-only mode */
175+
int do_nothing;
175176
} DumpOptions;
176177

177178
/*

‎src/bin/pg_dump/pg_dump.c

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -378,6 +378,7 @@ main(int argc, char **argv)
378378
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
379379
{"no-subscriptions", no_argument, &dopt.no_subscriptions, 1},
380380
{"no-sync", no_argument, NULL, 7},
381+
{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
381382

382383
{NULL, 0, NULL, 0}
383384
};
@@ -619,6 +620,9 @@ main(int argc, char **argv)
619620
if (dopt.if_exists && !dopt.outputClean)
620621
exit_horribly(NULL, "option --if-exists requires option -c/--clean\n");
621622

623+
if (dopt.do_nothing && !(dopt.dump_inserts || dopt.column_inserts))
624+
exit_horribly(NULL, "option --on-conflict-do-nothing requires option --inserts or --column-inserts\n");
625+
622626
/* Identify archive format to emit */
623627
archiveFormat = parseArchiveFormat(format, &archiveMode);
624628

@@ -989,6 +993,7 @@ help(const char *progname)
989993
printf(_(" --no-synchronized-snapshots do not use synchronized snapshots in parallel jobs\n"));
990994
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
991995
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
996+
printf(_(" --on-conflict-do-nothing add ON CONFLICT DO NOTHING to INSERT commands\n"));
992997
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
993998
printf(_(" --section=SECTION dump named section (pre-data, data, or post-data)\n"));
994999
printf(_(" --serializable-deferrable wait until the dump can run without anomalies\n"));
@@ -2047,7 +2052,11 @@ dumpTableData_insert(Archive *fout, void *dcontext)
20472052
break;
20482053
}
20492054
}
2050-
archputs(");\n", fout);
2055+
2056+
if (!dopt->do_nothing)
2057+
archputs(");\n", fout);
2058+
else
2059+
archputs(") ON CONFLICT DO NOTHING;\n", fout);
20512060
}
20522061

20532062
if (PQntuples(res) <= 0)

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@ static int no_unlogged_table_data = 0;
7878
static int no_role_passwords = 0;
7979
static int server_version;
8080
static int load_via_partition_root = 0;
81+
static int on_conflict_do_nothing = 0;
8182

8283
static char role_catalog[10];
8384
#define PG_AUTHID "pg_authid"
@@ -137,6 +138,7 @@ main(int argc, char *argv[])
137138
{"no-subscriptions", no_argument, &no_subscriptions, 1},
138139
{"no-sync", no_argument, NULL, 4},
139140
{"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1},
141+
{"on-conflict-do-nothing", no_argument, &on_conflict_do_nothing, 1},
140142

141143
{NULL, 0, NULL, 0}
142144
};
@@ -406,6 +408,8 @@ main(int argc, char *argv[])
406408
appendPQExpBufferStr(pgdumpopts, " --no-subscriptions");
407409
if (no_unlogged_table_data)
408410
appendPQExpBufferStr(pgdumpopts, " --no-unlogged-table-data");
411+
if (on_conflict_do_nothing)
412+
appendPQExpBufferStr(pgdumpopts, " --on-conflict-do-nothing");
409413

410414
/*
411415
* If there was a database specified on the command line, use that,
@@ -621,6 +625,7 @@ help(void)
621625
printf(_(" --no-sync do not wait for changes to be written safely to disk\n"));
622626
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
623627
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
628+
printf(_(" --on-conflict-do-nothing add ON CONFLICT DO NOTHING to INSERT commands\n"));
624629
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
625630
printf(_(" --use-set-session-authorization\n"
626631
" use SET SESSION AUTHORIZATION commands instead of\n"

‎src/bin/pg_dump/t/001_basic.pl

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
use Config;
55
use PostgresNode;
66
use TestLib;
7-
use Test::More tests => 68;
7+
use Test::More tests => 70;
88

99
my $tempdir = TestLib::tempdir;
1010
my $tempdir_short = TestLib::tempdir_short;
@@ -122,6 +122,11 @@
122122
qr/\Qpg_restore: unrecognized archive format "garbage";\E/,
123123
'pg_dump: unrecognized archive format');
124124

125+
command_fails_like(
126+
[ 'pg_dump', '--on-conflict-do-nothing' ],
127+
qr/\Qpg_dump: option --on-conflict-do-nothing requires option --inserts or --column-inserts\E/,
128+
'pg_dump: option --on-conflict-do-nothing requires option --inserts or --column-inserts');
129+
125130
# pg_dumpall command-line argument checks
126131
command_fails_like(
127132
[ 'pg_dumpall', '-g', '-r' ],

0 commit comments

Comments
 (0)