CREATE TEMP TABLE x (
	a serial,
	b int,
	c text not null default 'stuff',
	d text,
	e text
) WITH OIDS;
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();
ERROR:  Postgres-XL does not support TRIGGER yet
DETAIL:  The feature is not currently supported
CREATE TRIGGER trg_x_before BEFORE INSERT ON x
FOR EACH ROW EXECUTE PROCEDURE fn_x_before();
ERROR:  Postgres-XL does not support TRIGGER yet
DETAIL:  The feature is not currently supported
COPY x (a, b, c, d, e) from stdin;
COPY x (b, d) from stdin;
COPY x (b, d) from stdin;
COPY x (a, b, c, d, e) from stdin;
-- non-existent column in column list: should fail
COPY x (xyz) from stdin;
ERROR:  column "xyz" of relation "x" does not exist
-- 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 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 with oids delimiter ',' null 'x';
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii';
-- check results of copy in
SELECT * FROM x ORDER BY a, b;
   a   | b  |     c      |   d    | e  
-------+----+------------+--------+----
     1 |  1 | stuff      | test_1 | 
     2 |  2 | stuff      | test_2 | 
     3 |  3 | stuff      | test_3 | 
     4 |  4 | stuff      | test_4 | 
     5 |  5 | stuff      | test_5 | 
     6 |    | 45         | 80     | 90
     7 |    | x          | \x     | \x
     8 |    | ,          | \,     | \
  3000 |    | c          |        | 
  4000 |    | C          |        | 
  4001 |  1 | empty      |        | 
  4002 |  2 | null       |        | 
  4003 |  3 | Backslash  | \      | \
  4004 |  4 | BackslashX | \X     | \X
  4005 |  5 | N          | N      | N
  4006 |  6 | BackslashN | \N     | \N
  4007 |  7 | XX         | XX     | XX
  4008 |  8 | Delimiter  | :      | :
  9999 |    | \N         | NN     | 
 10000 | 21 | 31         | 41     | 51
 10001 | 22 | 32         | 42     | 52
 10002 | 23 | 33         | 43     | 53
 10003 | 24 | 34         | 44     | 54
 10004 | 25 | 35         | 45     | 55
 10005 | 26 | 36         | 46     | 56
(25 rows)

-- COPY w/ oids on a table w/o oids should fail
CREATE TABLE no_oids (
	a	int,
	b	int
) WITHOUT OIDS;
INSERT INTO no_oids (a, b) VALUES (5, 10);
INSERT INTO no_oids (a, b) VALUES (20, 30);
-- should fail
COPY no_oids FROM stdin WITH OIDS;
ERROR:  table "no_oids" does not have OIDs
COPY no_oids TO stdout WITH OIDS;
ERROR:  table "no_oids" does not have OIDs
-- check copy out
COPY x TO stdout;
9999	\N	\\N	NN	\N
1	1	stuff	test_1	\N
2	2	stuff	test_2	\N
5	5	stuff	test_5	\N
10001	22	32	42	52
10002	23	33	43	53
10004	25	35	45	55
6	\N	45	80	90
8	\N	,	\\,	\\
4000	\N	C	\N	\N
4002	2	null	\N	\N
4003	3	Backslash	\\	\\
4005	5	N	N	N
4007	7	XX	XX	XX
4008	8	Delimiter	:	:
10000	21	31	41	51
3	3	stuff	test_3	\N
4	4	stuff	test_4	\N
10003	24	34	44	54
10005	26	36	46	56
7	\N	x	\\x	\\x
3000	\N	c	\N	\N
4001	1	empty		
4004	4	BackslashX	\\X	\\X
4006	6	BackslashN	\\N	\\N
COPY x (c, e) TO stdout;
\\N	\N
stuff	\N
stuff	\N
stuff	\N
32	52
33	53
35	55
45	90
,	\\
C	\N
null	\N
Backslash	\\
N	N
XX	XX
Delimiter	:
31	51
stuff	\N
stuff	\N
34	54
36	56
x	\\x
c	\N
empty	
BackslashX	\\X
BackslashN	\\N
COPY x (b, e) TO stdout WITH NULL 'I''m null';
I'm null	I'm null
1	I'm null
2	I'm null
5	I'm null
22	52
23	53
25	55
I'm null	90
I'm null	\\
I'm null	I'm null
2	I'm null
3	\\
5	N
7	XX
8	:
21	51
3	I'm null
4	I'm null
24	54
26	56
I'm null	\\x
I'm null	I'm null
1	
4	\\X
6	\\N
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 ORDER BY 1,2;
 a  | b  
----+----
  1 | \0
 42 | \0
    | 
    | 
(4 rows)

BEGIN;
CREATE TABLE vistest (LIKE testeoc);
ERROR:  relation "testeoc" does not exist
COPY vistest FROM stdin CSV;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
a0
b
\.
invalid command \.
COMMIT;
ERROR:  syntax error at or near "a0"
LINE 1: a0
        ^
SELECT * FROM vistest;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
BEGIN;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
TRUNCATE vistest;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COPY vistest FROM stdin CSV;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
a1
b
\.
invalid command \.
SELECT * FROM vistest;
ERROR:  syntax error at or near "a1"
LINE 1: a1
        ^
SAVEPOINT s1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
TRUNCATE vistest;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COPY vistest FROM stdin CSV;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
d1
e
\.
invalid command \.
SELECT * FROM vistest;
ERROR:  syntax error at or near "d1"
LINE 1: d1
        ^
COMMIT;
SELECT * FROM vistest;
ERROR:  relation "vistest" does not exist
LINE 1: SELECT * FROM vistest;
                      ^
BEGIN;
TRUNCATE vistest;
ERROR:  relation "vistest" does not exist
COPY vistest FROM stdin CSV FREEZE;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
a2
b
\.
invalid command \.
SELECT * FROM vistest;
ERROR:  syntax error at or near "a2"
LINE 1: a2
        ^
SAVEPOINT s1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
TRUNCATE vistest;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COPY vistest FROM stdin CSV FREEZE;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
d2
e
\.
invalid command \.
SELECT * FROM vistest;
ERROR:  syntax error at or near "d2"
LINE 1: d2
        ^
COMMIT;
SELECT * FROM vistest;
ERROR:  relation "vistest" does not exist
LINE 1: SELECT * FROM vistest;
                      ^
BEGIN;
TRUNCATE vistest;
ERROR:  relation "vistest" does not exist
COPY vistest FROM stdin CSV FREEZE;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
x
y
\.
invalid command \.
SELECT * FROM vistest;
ERROR:  syntax error at or near "x"
LINE 1: x
        ^
COMMIT;
TRUNCATE vistest;
ERROR:  relation "vistest" does not exist
COPY vistest FROM stdin CSV FREEZE;
ERROR:  relation "vistest" does not exist
p
g
\.
invalid command \.
BEGIN;
ERROR:  syntax error at or near "p"
LINE 1: p
        ^
TRUNCATE vistest;
ERROR:  relation "vistest" does not exist
SAVEPOINT s1;
ERROR:  SAVEPOINT is not yet supported.
COPY vistest FROM stdin CSV FREEZE;
ERROR:  relation "vistest" does not exist
m
k
\.
invalid command \.
COMMIT;
ERROR:  syntax error at or near "m"
LINE 1: m
        ^
BEGIN;
INSERT INTO vistest VALUES ('z');
ERROR:  relation "vistest" does not exist
LINE 1: INSERT INTO vistest VALUES ('z');
                    ^
SAVEPOINT s1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
TRUNCATE vistest;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK TO SAVEPOINT s1;
ERROR:  no such savepoint
COPY vistest FROM stdin CSV FREEZE;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
d3
e
\.
invalid command \.
COMMIT;
ERROR:  syntax error at or near "d3"
LINE 1: d3
        ^
CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS
$$
BEGIN
	TRUNCATE vistest;
EXCEPTION
  WHEN OTHERS THEN
	INSERT INTO vistest VALUES ('subxact failure');
END;
$$ language plpgsql;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
BEGIN;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
INSERT INTO vistest VALUES ('z');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
SELECT truncate_in_subxact();
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COPY vistest FROM stdin CSV FREEZE;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
d4
e
\.
invalid command \.
SELECT * FROM vistest;
ERROR:  syntax error at or near "d4"
LINE 1: d4
        ^
COMMIT;
SELECT * FROM vistest;
ERROR:  relation "vistest" does not exist
LINE 1: SELECT * FROM vistest;
                      ^
-- 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 
---+---
   | 
(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 
---+---
   | 
(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" violates not-null constraint
DETAIL:  Failing row contains (3, null, , null, null).
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.*))
Distribute By: HASH(f1)
Location Nodes: ALL DATANODES

copy check_con_tbl from stdin;
copy check_con_tbl from stdin;
ERROR:  new row for relation "check_con_tbl" violates check constraint "check_con_tbl_check"
DETAIL:  Failing row contains (0).
select * from check_con_tbl;
 f1 
----
  1
   
(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);
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;
1	4	1
2	3	2
3	2	3
4	1	4
COPY rls_t1 (a, b, c) TO stdout;
1	4	1
2	3	2
3	2	3
4	1	4
-- subset of columns
COPY rls_t1 (a) TO stdout;
1
2
3
4
COPY rls_t1 (a, b) TO stdout;
1	4
2	3
3	2
4	1
-- column reordering
COPY rls_t1 (b, a) TO stdout;
4	1
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 relation rls_t1
COPY rls_t1 (a, b, c) TO stdout;
ERROR:  permission denied for relation rls_t1
-- try to copy column with no privileges (should fail)
COPY rls_t1 (c) TO stdout;
ERROR:  permission denied for relation 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
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
ERROR:  cannot copy to view "instead_of_insert_tbl_view"
HINT:  To enable copying to a view, provide an INSTEAD OF INSERT trigger.
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();
ERROR:  Postgres-XL does not support TRIGGER yet
DETAIL:  The feature is not currently supported
COPY instead_of_insert_tbl_view FROM stdin;
ERROR:  cannot copy to view "instead_of_insert_tbl_view"
HINT:  To enable copying to a view, provide an INSTEAD OF INSERT trigger.
SELECT * FROM instead_of_insert_tbl;
 id | name 
----+------
(0 rows)

-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
ERROR:  table "vistest" does not exist
DROP FUNCTION truncate_in_subxact();
ERROR:  function truncate_in_subxact() does not exist
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();
DROP TABLE instead_of_insert_tbl;
DROP VIEW instead_of_insert_tbl_view;
DROP FUNCTION fun_instead_of_insert_tbl();