From 9f8377f7a27910bf0f35bf5169a8046731948a79 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan
Date: Mon, 13 Mar 2023 10:01:56 -0400
Subject: Add a DEFAULT option to COPY  FROM

This allows for a string which if an input field matches causes the
column's default value to be inserted. The advantage of this is that
the default can be inserted in some rows and not others, for which
non-default data is available.

The file_fdw extension is also modified to take allow use of this
option.

Israel Barth Rubio

Discussion: https://postgr.es/m/CAO_rXXAcqesk6DsvioOZ5zmeEmpUN5ktZf-9=9yu+DTr0Xr8Uw@mail.gmail.com
---
 src/bin/psql/t/001_basic.pl          | 25 +++++++++++++++++++++++++
 src/bin/psql/t/010_tab_completion.pl |  8 ++++++++
 src/bin/psql/tab-complete.c          |  2 +-
 3 files changed, 34 insertions(+), 1 deletion(-)

(limited to 'src/bin')

diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index 0167cb58a2e..0f394420b26 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -325,4 +325,29 @@ is($row_count, '10',
 	'client-side error commits transaction, no ON_ERROR_STOP and multiple -c switches'
 );
 
+# Test \copy from with DEFAULT option
+$node->safe_psql(
+	'postgres',
+	"CREATE TABLE copy_default (
+		id integer PRIMARY KEY,
+		text_value text NOT NULL DEFAULT 'test',
+		ts_value timestamp without time zone NOT NULL DEFAULT '2022-07-05'
+	)"
+);
+
+my $copy_default_sql_file = "$tempdir/copy_default.csv";
+append_to_file($copy_default_sql_file, "1,value,2022-07-04\n");
+append_to_file($copy_default_sql_file, "2,placeholder,2022-07-03\n");
+append_to_file($copy_default_sql_file, "3,placeholder,placeholder\n");
+
+psql_like(
+	$node,
+	"\\copy copy_default from $copy_default_sql_file with (format 'csv', default 'placeholder');
+	SELECT * FROM copy_default",
+	qr/1\|value\|2022-07-04 00:00:00
+2|test|2022-07-03 00:00:00
+3|test|2022-07-05 00:00:00/,
+	'\copy from with DEFAULT'
+);
+
 done_testing();
diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl
index 7746c75e0c9..55a88f98122 100644
--- a/src/bin/psql/t/010_tab_completion.pl
+++ b/src/bin/psql/t/010_tab_completion.pl
@@ -442,6 +442,14 @@ check_completion("blarg \t\t", qr//, "check completion failure path");
 
 clear_query();
 
+# check COPY FROM with DEFAULT option
+check_completion(
+	"COPY foo FROM stdin WITH ( DEF\t)",
+	qr/DEFAULT /,
+	"COPY FROM with DEFAULT completion");
+
+clear_line();
+
 # send psql an explicit \q to shut it down, else pty won't close properly
 $timer->start($PostgreSQL::Test::Utils::timeout_default);
 $in .= "\\q\n";
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8f12af799be..42e87b9e490 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2857,7 +2857,7 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING");
+					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
-- 
cgit v1.2.3