src/test/regress/expected/copy2.out

CREATE TEMP TABLE x (
	a serial,
	b int,
	c text not null default 'stuff',
	d text,
	e text
);
CREATE FUNCTION fn_x_before () RETURNS TRIGGER AS '
  BEGIN
		NEW.e := ''before trigger fired''::text;
		return NEW;
	END;
' LANGUAGE plpgsql;
CREATE FUNCTION fn_x_after () RETURNS TRIGGER AS '
  BEGIN
		UPDATE x set e=''after trigger fired'' where c=''stuff'';
		return NULL;
	END;
' LANGUAGE plpgsql;
CREATE TRIGGER trg_x_after AFTER INSERT ON x
FOR EACH ROW EXECUTE PROCEDURE fn_x_after();
CREATE TRIGGER trg_x_before BEFORE INSERT ON x
FOR EACH ROW EXECUTE PROCEDURE fn_x_before();
COPY x (a, b, c, d, e) from stdin;
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
COPY x (b, d) from stdin;
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
COPY x (b, d) from stdin;
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
COPY x (a, b, c, d, e) from stdin;
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
-- non-existent column in column list: should fail
COPY x (xyz) from stdin;
ERROR:  column "xyz" of relation "x" does not exist
-- redundant options
COPY x from stdin (format CSV, FORMAT CSV);
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (format CSV, FORMAT CSV);
                                       ^
COPY x from stdin (freeze off, freeze on);
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (freeze off, freeze on);
                                       ^
COPY x from stdin (delimiter ',', delimiter ',');
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (delimiter ',', delimiter ',');
                                          ^
COPY x from stdin (null ' ', null ' ');
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (null ' ', null ' ');
                                     ^
COPY x from stdin (header off, header on);
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (header off, header on);
                                       ^
COPY x from stdin (quote ':', quote ':');
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (quote ':', quote ':');
                                      ^
COPY x from stdin (escape ':', escape ':');
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (escape ':', escape ':');
                                       ^
COPY x from stdin (force_quote (a), force_quote *);
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (force_quote (a), force_quote *);
                                            ^
COPY x from stdin (force_not_null (a), force_not_null (b));
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (force_not_null (a), force_not_null (b));
                                               ^
COPY x from stdin (force_null (a), force_null (b));
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (force_null (a), force_null (b));
                                           ^
COPY x from stdin (convert_selectively (a), convert_selectively (b));
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (convert_selectively (a), convert_selectiv...
                                                    ^
COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
ERROR:  conflicting or redundant options
LINE 1: COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii...
                                                 ^
-- incorrect options
COPY x from stdin (format BINARY, delimiter ',');
ERROR:  COPY cannot specify DELIMITER in BINARY mode
COPY x from stdin (format BINARY, null 'x');
ERROR:  COPY cannot specify NULL in BINARY mode
COPY x from stdin (format TEXT, force_quote(a));
ERROR:  COPY force quote available only in CSV mode
COPY x from stdin (format CSV, force_quote(a));
ERROR:  COPY force quote only available using COPY TO
COPY x from stdin (format TEXT, force_not_null(a));
ERROR:  COPY force not null available only in CSV mode
COPY x to stdout (format CSV, force_not_null(a));
ERROR:  COPY force not null only available using COPY FROM
COPY x from stdin (format TEXT, force_null(a));
ERROR:  COPY force null available only in CSV mode
COPY x to stdout (format CSV, force_null(a));
ERROR:  COPY force null only available using COPY FROM
-- too many columns in column list: should fail
COPY x (a, b, c, d, e, d, c) from stdin;
ERROR:  column "d" specified more than once
-- missing data: should fail
COPY x from stdin;
ERROR:  invalid input syntax for type integer: ""
CONTEXT:  COPY x, line 1, column a: ""
COPY x from stdin;
ERROR:  missing data for column "e"
CONTEXT:  COPY x, line 1: "2000	230	23	23"
COPY x from stdin;
ERROR:  missing data for column "e"
CONTEXT:  COPY x, line 1: "2001	231	\N	\N"
-- extra data: should fail
COPY x from stdin;
ERROR:  extra data after last expected column
CONTEXT:  COPY x, line 1: "2002	232	40	50	60	70	80"
-- various COPY options: delimiters, oids, NULL string, encoding
COPY x (b, c, d, e) from stdin delimiter ',' null 'x';
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii';
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
COPY x TO stdout WHERE a = 1;
ERROR:  WHERE clause not allowed with COPY TO
LINE 1: COPY x TO stdout WHERE a = 1;
                         ^
COPY x from stdin WHERE a = 50004;
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
COPY x from stdin WHERE a > 60003;
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement
CONTEXT:  SQL statement "UPDATE x set e='after trigger fired' where c='stuff'"
PL/pgSQL function fn_x_after() line 3 at SQL statement
COPY x from stdin WHERE f > 60003;
ERROR:  column "f" does not exist
LINE 1: COPY x from stdin WHERE f > 60003;
                                ^
COPY x from stdin WHERE a = max(x.b);
ERROR:  aggregate functions are not allowed in COPY FROM WHERE conditions
LINE 1: COPY x from stdin WHERE a = max(x.b);
                                    ^
COPY x from stdin WHERE a IN (SELECT 1 FROM x);
ERROR:  cannot use subquery in COPY FROM WHERE condition
LINE 1: COPY x from stdin WHERE a IN (SELECT 1 FROM x);
                                  ^
COPY x from stdin WHERE a IN (generate_series(1,5));
ERROR:  set-returning functions are not allowed in COPY FROM WHERE conditions
LINE 1: COPY x from stdin WHERE a IN (generate_series(1,5));
                                      ^
COPY x from stdin WHERE a = row_number() over(b);
ERROR:  window functions are not allowed in COPY FROM WHERE conditions
LINE 1: COPY x from stdin WHERE a = row_number() over(b);
                                    ^
-- check results of copy in
SELECT * FROM x;
 a | b | c | d | e 
---+---+---+---+---
(0 rows)

-- check copy out
COPY x TO stdout;
COPY x (c, e) TO stdout;
COPY x (b, e) TO stdout WITH NULL 'I''m null';
CREATE TEMP TABLE y (
	col1 text,
	col2 text
);
INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
INSERT INTO y VALUES ('It is "perfect".',E'\t');
INSERT INTO y VALUES ('', NULL);
COPY y TO stdout WITH CSV;
"Jackson, Sam",\h
"It is ""perfect"".",	
"",
COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
Jackson, Sam|\h
It is "perfect".|	
''|
COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\' ENCODING 'sql_ascii';
"Jackson, Sam","\\h"
"It is \"perfect\".","	"
"",
COPY y TO stdout WITH CSV FORCE QUOTE *;
"Jackson, Sam","\h"
"It is ""perfect"".","	"
"",
-- Repeat above tests with new 9.0 option syntax
COPY y TO stdout (FORMAT CSV);
"Jackson, Sam",\h
"It is ""perfect"".",	
"",
COPY y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|');
Jackson, Sam|\h
It is "perfect".|	
''|
COPY y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\');
"Jackson, Sam","\\h"
"It is \"perfect\".","	"
"",
COPY y TO stdout (FORMAT CSV, FORCE_QUOTE *);
"Jackson, Sam","\h"
"It is ""perfect"".","	"
"",
\copy y TO stdout (FORMAT CSV)
"Jackson, Sam",\h
"It is ""perfect"".",	
"",
\copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|')
Jackson, Sam|\h
It is "perfect".|	
''|
\copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\')
"Jackson, Sam","\\h"
"It is \"perfect\".","	"
"",
\copy y TO stdout (FORMAT CSV, FORCE_QUOTE *)
"Jackson, Sam","\h"
"It is ""perfect"".","	"
"",
--test that we read consecutive LFs properly
CREATE TEMP TABLE testnl (a int, b text, c int);
COPY testnl FROM stdin CSV;
-- test end of copy marker
CREATE TEMP TABLE testeoc (a text);
COPY testeoc FROM stdin CSV;
COPY testeoc TO stdout CSV;
a\.
\.b
c\.d
"\."
-- test handling of nonstandard null marker that violates escaping rules
CREATE TEMP TABLE testnull(a int, b text);
INSERT INTO testnull VALUES (1, E'\\0'), (NULL, NULL);
COPY testnull TO stdout WITH NULL AS E'\\0';
1	\\0
\0	\0
COPY testnull FROM stdin WITH NULL AS E'\\0';
SELECT * FROM testnull;
 a  | b  
----+----
  1 | \0
    | 
 42 | \0
    | 
(4 rows)

BEGIN;
CREATE TABLE vistest (LIKE testeoc);
COPY vistest FROM stdin CSV;
COMMIT;
SELECT * FROM vistest;
 a  
----
 a0
 b
(2 rows)

BEGIN;
TRUNCATE vistest;
COPY vistest FROM stdin CSV;
SELECT * FROM vistest;
 a  
----
 a1
 b
(2 rows)

SAVEPOINT s1;
TRUNCATE vistest;
COPY vistest FROM stdin CSV;
SELECT * FROM vistest;
 a  
----
 d1
 e
(2 rows)

COMMIT;
SELECT * FROM vistest;
 a  
----
 d1
 e
(2 rows)

BEGIN;
TRUNCATE vistest;
COPY vistest FROM stdin CSV FREEZE;
SELECT * FROM vistest;
 a  
----
 a2
 b
(2 rows)

SAVEPOINT s1;
TRUNCATE vistest;
COPY vistest FROM stdin CSV FREEZE;
SELECT * FROM vistest;
 a  
----
 d2
 e
(2 rows)

COMMIT;
SELECT * FROM vistest;
 a  
----
 d2
 e
(2 rows)

BEGIN;
TRUNCATE vistest;
COPY vistest FROM stdin CSV FREEZE;
SELECT * FROM vistest;
 a 
---
 x
 y
(2 rows)

COMMIT;
TRUNCATE vistest;
COPY vistest FROM stdin CSV FREEZE;
ERROR:  cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction
BEGIN;
TRUNCATE vistest;
SAVEPOINT s1;
COPY vistest FROM stdin CSV FREEZE;
ERROR:  cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction
COMMIT;
BEGIN;
INSERT INTO vistest VALUES ('z');
SAVEPOINT s1;
TRUNCATE vistest;
ROLLBACK TO SAVEPOINT s1;
COPY vistest FROM stdin CSV FREEZE;
ERROR:  cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction
COMMIT;
CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS
$$
BEGIN
	TRUNCATE vistest;
EXCEPTION
  WHEN OTHERS THEN
	INSERT INTO vistest VALUES ('subxact failure');
END;
$$ language plpgsql;
BEGIN;
INSERT INTO vistest VALUES ('z');
SELECT truncate_in_subxact();
 truncate_in_subxact 
---------------------
 
(1 row)

COPY vistest FROM stdin CSV FREEZE;
SELECT * FROM vistest;
 a  
----
 d4
 e
(2 rows)

COMMIT;
SELECT * FROM vistest;
 a  
----
 d4
 e
(2 rows)

-- Test FORCE_NOT_NULL and FORCE_NULL options
CREATE TEMP TABLE forcetest (
    a INT NOT NULL,
    b TEXT NOT NULL,
    c TEXT,
    d TEXT,
    e TEXT
);
\pset null NULL
-- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
BEGIN;
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
COMMIT;
SELECT b, c FROM forcetest WHERE a = 1;
 b |  c   
---+------
   | NULL
(1 row)

-- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified
BEGIN;
COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d));
COMMIT;
SELECT c, d FROM forcetest WHERE a = 2;
 c |  d   
---+------
   | NULL
(1 row)

-- should fail with not-null constraint violation
BEGIN;
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b), FORCE_NOT_NULL(c));
ERROR:  null value in column "b" of relation "forcetest" violates not-null constraint
DETAIL:  Failing row contains (3, null, , null, null).
CONTEXT:  COPY forcetest, line 1: "3,,"""
ROLLBACK;
-- should fail with "not referenced by COPY" error
BEGIN;
COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b));
ERROR:  FORCE_NOT_NULL column "b" not referenced by COPY
ROLLBACK;
-- should fail with "not referenced by COPY" error
BEGIN;
COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b));
ERROR:  FORCE_NULL column "b" not referenced by COPY
ROLLBACK;
\pset null ''
-- test case with whole-row Var in a check constraint
create table check_con_tbl (f1 int);
create function check_con_function(check_con_tbl) returns bool as $$
begin
  raise notice 'input = %', row_to_json($1);
  return $1.f1 > 0;
end $$ language plpgsql immutable;
alter table check_con_tbl add check (check_con_function(check_con_tbl.*));
\d+ check_con_tbl
                               Table "public.check_con_tbl"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 f1     | integer |           |          |         | plain   |              | 
Check constraints:
    "check_con_tbl_check" CHECK (check_con_function(check_con_tbl.*))
Distributed by: (f1)

-- GPDB: Change from 1 (value in PG) to 2 for copy to make test deterministic.
-- 2 and null are on seg0 in a 3-seg test environment.
copy check_con_tbl from stdin;
NOTICE:  input = {"f1":2}
NOTICE:  input = {"f1":null}
copy check_con_tbl from stdin;
NOTICE:  input = {"f1":0}
ERROR:  new row for relation "check_con_tbl" violates check constraint "check_con_tbl_check"
DETAIL:  Failing row contains (0).
CONTEXT:  COPY check_con_tbl, line 1: "0"
select * from check_con_tbl;
 f1 
----
  2
   
(2 rows)

-- test with RLS enabled.
CREATE ROLE regress_rls_copy_user;
CREATE ROLE regress_rls_copy_user_colperms;
CREATE TABLE rls_t1 (a int, b int, c int);
-- GPDB: Change from 1 (value in PG) to 3 for copy to make test deterministic.
-- 2, 3 and 4 are on seg0 in a 3-seg test environment.
COPY rls_t1 (a, b, c) from stdin;
CREATE POLICY p1 ON rls_t1 FOR SELECT USING (a % 2 = 0);
ALTER TABLE rls_t1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_t1 FORCE ROW LEVEL SECURITY;
GRANT SELECT ON TABLE rls_t1 TO regress_rls_copy_user;
GRANT SELECT (a, b) ON TABLE rls_t1 TO regress_rls_copy_user_colperms;
-- all columns
COPY rls_t1 TO stdout;
3	4	1
2	3	2
3	2	3
4	1	4
COPY rls_t1 (a, b, c) TO stdout;
3	4	1
2	3	2
3	2	3
4	1	4
-- subset of columns
COPY rls_t1 (a) TO stdout;
3
2
3
4
COPY rls_t1 (a, b) TO stdout;
3	4
2	3
3	2
4	1
-- column reordering
COPY rls_t1 (b, a) TO stdout;
4	3
3	2
2	3
1	4
SET SESSION AUTHORIZATION regress_rls_copy_user;
-- all columns
COPY rls_t1 TO stdout;
2	3	2
4	1	4
COPY rls_t1 (a, b, c) TO stdout;
2	3	2
4	1	4
-- subset of columns
COPY rls_t1 (a) TO stdout;
2
4
COPY rls_t1 (a, b) TO stdout;
2	3
4	1
-- column reordering
COPY rls_t1 (b, a) TO stdout;
3	2
1	4
RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION regress_rls_copy_user_colperms;
-- attempt all columns (should fail)
COPY rls_t1 TO stdout;
ERROR:  permission denied for table rls_t1
COPY rls_t1 (a, b, c) TO stdout;
ERROR:  permission denied for table rls_t1
-- try to copy column with no privileges (should fail)
COPY rls_t1 (c) TO stdout;
ERROR:  permission denied for table rls_t1
-- subset of columns (should succeed)
COPY rls_t1 (a) TO stdout;
2
4
COPY rls_t1 (a, b) TO stdout;
2	3
4	1
RESET SESSION AUTHORIZATION;
-- test with INSTEAD OF INSERT trigger on a view
-- INSTEAD OF triggers are not supported in Cloudberry
/*
 * CREATE TABLE instead_of_insert_tbl(id serial, name text);
 * CREATE VIEW instead_of_insert_tbl_view AS SELECT ''::text AS str;
 * 
 * COPY instead_of_insert_tbl_view FROM stdin; -- fail
 * test1
 * \.
 * 
 * CREATE FUNCTION fun_instead_of_insert_tbl() RETURNS trigger AS $$
 * BEGIN
 *   INSERT INTO instead_of_insert_tbl (name) VALUES (NEW.str);
 *   RETURN NULL;
 * END;
 * $$ LANGUAGE plpgsql;
 * CREATE TRIGGER trig_instead_of_insert_tbl_view
 *   INSTEAD OF INSERT ON instead_of_insert_tbl_view
 *   FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl();
 * 
 * COPY instead_of_insert_tbl_view FROM stdin;
 * test1
 * \.
 * 
 * SELECT * FROM instead_of_insert_tbl;
 */
-- Test of COPY optimization with view using INSTEAD OF INSERT
-- trigger when relation is created in the same transaction as
-- when COPY is executed.
-- INSTEAD OF triggers are not supported in Cloudberry
/*
 * BEGIN;
 * CREATE VIEW instead_of_insert_tbl_view_2 as select ''::text as str;
 * CREATE TRIGGER trig_instead_of_insert_tbl_view_2
 *   INSTEAD OF INSERT ON instead_of_insert_tbl_view_2
 *   FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl();
 * 
 * COPY instead_of_insert_tbl_view_2 FROM stdin;
 * test1
 * \.
 * 
 * SELECT * FROM instead_of_insert_tbl;
 * COMMIT;
 */
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
DROP FUNCTION truncate_in_subxact();
DROP TABLE x, y;
DROP TABLE rls_t1 CASCADE;
DROP ROLE regress_rls_copy_user;
DROP ROLE regress_rls_copy_user_colperms;
DROP FUNCTION fn_x_before();
DROP FUNCTION fn_x_after();
-- When error reject limit is set, copy should be able to continue after hit a corrupted end-of-copy marker 
CREATE TABLE copy_eoc_marker(a int, b int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
COPY copy_eoc_marker FROM stdin LOG ERRORS SEGMENT REJECT LIMIT 5;
NOTICE:  found 2 data formatting errors (2 or more input rows), rejected related input data
SELECT * FROM copy_eoc_marker;
  a  | b  
-----+----
 123 | 20
(1 row)

DROP TABLE copy_eoc_marker;
--
-- COPY FROM ... DEFAULT
--
create temp 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'
);
-- if DEFAULT is not specified, then the marker will be regular data
copy copy_default from stdin;
select id, text_value, ts_value from copy_default;
 id | text_value |         ts_value         
----+------------+--------------------------
  1 | value      | Mon Jul 04 00:00:00 2022
  2 | D          | Tue Jul 05 00:00:00 2022
(2 rows)

truncate copy_default;
copy copy_default from stdin with (format csv);
select id, text_value, ts_value from copy_default;
 id | text_value |         ts_value         
----+------------+--------------------------
  1 | value      | Mon Jul 04 00:00:00 2022
  2 | \D         | Tue Jul 05 00:00:00 2022
(2 rows)

truncate copy_default;
-- DEFAULT cannot be used in binary mode
copy copy_default from stdin with (format binary, default '\D');
ERROR:  cannot specify DEFAULT in BINARY mode
-- DEFAULT cannot be new line nor carriage return
copy copy_default from stdin with (default E'\n');
ERROR:  COPY default representation cannot use newline or carriage return
copy copy_default from stdin with (default E'\r');
ERROR:  COPY default representation cannot use newline or carriage return
-- DELIMITER cannot appear in DEFAULT spec
copy copy_default from stdin with (delimiter ';', default 'test;test');
ERROR:  COPY delimiter must not appear in the DEFAULT specification
-- CSV quote cannot appear in DEFAULT spec
copy copy_default from stdin with (format csv, quote '"', default 'test"test');
ERROR:  CSV quote character must not appear in the DEFAULT specification
-- NULL and DEFAULT spec must be different
copy copy_default from stdin with (default '\N');
ERROR:  NULL specification and DEFAULT specification cannot be the same
-- cannot use DEFAULT marker in column that has no DEFAULT value
copy copy_default from stdin with (default '\D');
ERROR:  unexpected default marker in COPY data
DETAIL:  Column "id" has no default value.
CONTEXT:  COPY copy_default, line 1: "\D	value	'2022-07-04'"
copy copy_default from stdin with (format csv, default '\D');
ERROR:  unexpected default marker in COPY data
DETAIL:  Column "id" has no default value.
CONTEXT:  COPY copy_default, line 1: "\D,value,2022-07-04"
-- The DEFAULT marker must be unquoted and unescaped or it's not recognized
copy copy_default from stdin with (default '\D');
select id, text_value, ts_value from copy_default;
 id | text_value |         ts_value         
----+------------+--------------------------
  1 | test       | Mon Jul 04 00:00:00 2022
  2 | \D         | Mon Jul 04 00:00:00 2022
  3 | "D"        | Mon Jul 04 00:00:00 2022
(3 rows)

truncate copy_default;
copy copy_default from stdin with (format csv, default '\D');
select id, text_value, ts_value from copy_default;
 id | text_value |         ts_value         
----+------------+--------------------------
  1 | test       | Mon Jul 04 00:00:00 2022
  2 | \\D        | Mon Jul 04 00:00:00 2022
  3 | \D         | Mon Jul 04 00:00:00 2022
(3 rows)

truncate copy_default;
-- successful usage of DEFAULT option in COPY
copy copy_default from stdin with (default '\D');
select id, text_value, ts_value from copy_default;
 id | text_value |         ts_value         
----+------------+--------------------------
  1 | value      | Mon Jul 04 00:00:00 2022
  2 | test       | Sun Jul 03 00:00:00 2022
  3 | test       | Tue Jul 05 00:00:00 2022
(3 rows)

truncate copy_default;
copy copy_default from stdin with (format csv, default '\D');
select id, text_value, ts_value from copy_default;
 id | text_value |         ts_value         
----+------------+--------------------------
  1 | value      | Mon Jul 04 00:00:00 2022
  2 | test       | Sun Jul 03 00:00:00 2022
  3 | test       | Tue Jul 05 00:00:00 2022
(3 rows)

truncate copy_default;
-- DEFAULT cannot be used in COPY TO
copy (select 1 as test) TO stdout with (default '\D');
ERROR:  COPY DEFAULT only available using COPY FROM