diff options
Diffstat (limited to 'src/test/regress/expected/copy2.out')
| -rw-r--r-- | src/test/regress/expected/copy2.out | 616 |
1 files changed, 616 insertions, 0 deletions
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out new file mode 100644 index 0000000..e40287d --- /dev/null +++ b/src/test/regress/expected/copy2.out @@ -0,0 +1,616 @@ +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; +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 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'; +COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; +COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii'; +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; +COPY x from stdin WHERE a > 60003; +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 +-------+----+------------+--------+---------------------- + 9999 | | \N | NN | before trigger fired + 10000 | 21 | 31 | 41 | before trigger fired + 10001 | 22 | 32 | 42 | before trigger fired + 10002 | 23 | 33 | 43 | before trigger fired + 10003 | 24 | 34 | 44 | before trigger fired + 10004 | 25 | 35 | 45 | before trigger fired + 10005 | 26 | 36 | 46 | before trigger fired + 6 | | 45 | 80 | before trigger fired + 7 | | x | \x | before trigger fired + 8 | | , | \, | before trigger fired + 3000 | | c | | before trigger fired + 4000 | | C | | before trigger fired + 4001 | 1 | empty | | before trigger fired + 4002 | 2 | null | | before trigger fired + 4003 | 3 | Backslash | \ | before trigger fired + 4004 | 4 | BackslashX | \X | before trigger fired + 4005 | 5 | N | N | before trigger fired + 4006 | 6 | BackslashN | \N | before trigger fired + 4007 | 7 | XX | XX | before trigger fired + 4008 | 8 | Delimiter | : | before trigger fired + 50004 | 25 | 35 | 45 | before trigger fired + 60004 | 25 | 35 | 45 | before trigger fired + 60005 | 26 | 36 | 46 | before trigger fired + 1 | 1 | stuff | test_1 | after trigger fired + 2 | 2 | stuff | test_2 | after trigger fired + 3 | 3 | stuff | test_3 | after trigger fired + 4 | 4 | stuff | test_4 | after trigger fired + 5 | 5 | stuff | test_5 | after trigger fired +(28 rows) + +-- check copy out +COPY x TO stdout; +9999 \N \\N NN before trigger fired +10000 21 31 41 before trigger fired +10001 22 32 42 before trigger fired +10002 23 33 43 before trigger fired +10003 24 34 44 before trigger fired +10004 25 35 45 before trigger fired +10005 26 36 46 before trigger fired +6 \N 45 80 before trigger fired +7 \N x \\x before trigger fired +8 \N , \\, before trigger fired +3000 \N c \N before trigger fired +4000 \N C \N before trigger fired +4001 1 empty before trigger fired +4002 2 null \N before trigger fired +4003 3 Backslash \\ before trigger fired +4004 4 BackslashX \\X before trigger fired +4005 5 N N before trigger fired +4006 6 BackslashN \\N before trigger fired +4007 7 XX XX before trigger fired +4008 8 Delimiter : before trigger fired +50004 25 35 45 before trigger fired +60004 25 35 45 before trigger fired +60005 26 36 46 before trigger fired +1 1 stuff test_1 after trigger fired +2 2 stuff test_2 after trigger fired +3 3 stuff test_3 after trigger fired +4 4 stuff test_4 after trigger fired +5 5 stuff test_5 after trigger fired +COPY x (c, e) TO stdout; +\\N before trigger fired +31 before trigger fired +32 before trigger fired +33 before trigger fired +34 before trigger fired +35 before trigger fired +36 before trigger fired +45 before trigger fired +x before trigger fired +, before trigger fired +c before trigger fired +C before trigger fired +empty before trigger fired +null before trigger fired +Backslash before trigger fired +BackslashX before trigger fired +N before trigger fired +BackslashN before trigger fired +XX before trigger fired +Delimiter before trigger fired +35 before trigger fired +35 before trigger fired +36 before trigger fired +stuff after trigger fired +stuff after trigger fired +stuff after trigger fired +stuff after trigger fired +stuff after trigger fired +COPY x (b, e) TO stdout WITH NULL 'I''m null'; +I'm null before trigger fired +21 before trigger fired +22 before trigger fired +23 before trigger fired +24 before trigger fired +25 before trigger fired +26 before trigger fired +I'm null before trigger fired +I'm null before trigger fired +I'm null before trigger fired +I'm null before trigger fired +I'm null before trigger fired +1 before trigger fired +2 before trigger fired +3 before trigger fired +4 before trigger fired +5 before trigger fired +6 before trigger fired +7 before trigger fired +8 before trigger fired +25 before trigger fired +25 before trigger fired +26 before trigger fired +1 after trigger fired +2 after trigger fired +3 after trigger fired +4 after trigger fired +5 after trigger fired +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.*)) + +copy check_con_tbl from stdin; +NOTICE: input = {"f1":1} +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 +---- + 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 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 +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(); +COPY instead_of_insert_tbl_view FROM stdin; +SELECT * FROM instead_of_insert_tbl; + id | name +----+------- + 1 | test1 +(1 row) + +-- Test of COPY optimization with view using INSTEAD OF INSERT +-- trigger when relation is created in the same transaction as +-- when COPY is executed. +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; +SELECT * FROM instead_of_insert_tbl; + id | name +----+------- + 1 | test1 + 2 | test1 +(2 rows) + +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(); +DROP TABLE instead_of_insert_tbl; +DROP VIEW instead_of_insert_tbl_view; +DROP VIEW instead_of_insert_tbl_view_2; +DROP FUNCTION fun_instead_of_insert_tbl(); |