1CREATE TEMP TABLE x ( 2 a serial, 3 b int, 4 c text not null default 'stuff', 5 d text, 6 e text 7) WITH OIDS; 8 9CREATE FUNCTION fn_x_before () RETURNS TRIGGER AS ' 10 BEGIN 11 NEW.e := ''before trigger fired''::text; 12 return NEW; 13 END; 14' LANGUAGE plpgsql; 15 16CREATE FUNCTION fn_x_after () RETURNS TRIGGER AS ' 17 BEGIN 18 UPDATE x set e=''after trigger fired'' where c=''stuff''; 19 return NULL; 20 END; 21' LANGUAGE plpgsql; 22 23CREATE TRIGGER trg_x_after AFTER INSERT ON x 24FOR EACH ROW EXECUTE PROCEDURE fn_x_after(); 25 26CREATE TRIGGER trg_x_before BEFORE INSERT ON x 27FOR EACH ROW EXECUTE PROCEDURE fn_x_before(); 28 29COPY x (a, b, c, d, e) from stdin; 309999 \N \\N \NN \N 3110000 21 31 41 51 32\. 33 34COPY x (b, d) from stdin; 351 test_1 36\. 37 38COPY x (b, d) from stdin; 392 test_2 403 test_3 414 test_4 425 test_5 43\. 44 45COPY x (a, b, c, d, e) from stdin; 4610001 22 32 42 52 4710002 23 33 43 53 4810003 24 34 44 54 4910004 25 35 45 55 5010005 26 36 46 56 51\. 52 53-- non-existent column in column list: should fail 54COPY x (xyz) from stdin; 55 56-- too many columns in column list: should fail 57COPY x (a, b, c, d, e, d, c) from stdin; 58 59-- missing data: should fail 60COPY x from stdin; 61 62\. 63COPY x from stdin; 642000 230 23 23 65\. 66COPY x from stdin; 672001 231 \N \N 68\. 69 70-- extra data: should fail 71COPY x from stdin; 722002 232 40 50 60 70 80 73\. 74 75-- various COPY options: delimiters, oids, NULL string, encoding 76COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x'; 77500000,x,45,80,90 78500001,x,\x,\\x,\\\x 79500002,x,\,,\\\,,\\ 80\. 81 82COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; 833000;;c;; 84\. 85 86COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii'; 874000:\X:C:\X:\X 884001:1:empty:: 894002:2:null:\X:\X 904003:3:Backslash:\\:\\ 914004:4:BackslashX:\\X:\\X 924005:5:N:\N:\N 934006:6:BackslashN:\\N:\\N 944007:7:XX:\XX:\XX 954008:8:Delimiter:\::\: 96\. 97 98-- check results of copy in 99SELECT * FROM x; 100 101-- COPY w/ oids on a table w/o oids should fail 102CREATE TABLE no_oids ( 103 a int, 104 b int 105) WITHOUT OIDS; 106 107INSERT INTO no_oids (a, b) VALUES (5, 10); 108INSERT INTO no_oids (a, b) VALUES (20, 30); 109 110-- should fail 111COPY no_oids FROM stdin WITH OIDS; 112COPY no_oids TO stdout WITH OIDS; 113 114-- check copy out 115COPY x TO stdout; 116COPY x (c, e) TO stdout; 117COPY x (b, e) TO stdout WITH NULL 'I''m null'; 118 119CREATE TEMP TABLE y ( 120 col1 text, 121 col2 text 122); 123 124INSERT INTO y VALUES ('Jackson, Sam', E'\\h'); 125INSERT INTO y VALUES ('It is "perfect".',E'\t'); 126INSERT INTO y VALUES ('', NULL); 127 128COPY y TO stdout WITH CSV; 129COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|'; 130COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\' ENCODING 'sql_ascii'; 131COPY y TO stdout WITH CSV FORCE QUOTE *; 132 133-- Repeat above tests with new 9.0 option syntax 134 135COPY y TO stdout (FORMAT CSV); 136COPY y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|'); 137COPY y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\'); 138COPY y TO stdout (FORMAT CSV, FORCE_QUOTE *); 139 140\copy y TO stdout (FORMAT CSV) 141\copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|') 142\copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\') 143\copy y TO stdout (FORMAT CSV, FORCE_QUOTE *) 144 145--test that we read consecutive LFs properly 146 147CREATE TEMP TABLE testnl (a int, b text, c int); 148 149COPY testnl FROM stdin CSV; 1501,"a field with two LFs 151 152inside",2 153\. 154 155-- test end of copy marker 156CREATE TEMP TABLE testeoc (a text); 157 158COPY testeoc FROM stdin CSV; 159a\. 160\.b 161c\.d 162"\." 163\. 164 165COPY testeoc TO stdout CSV; 166 167-- test handling of nonstandard null marker that violates escaping rules 168 169CREATE TEMP TABLE testnull(a int, b text); 170INSERT INTO testnull VALUES (1, E'\\0'), (NULL, NULL); 171 172COPY testnull TO stdout WITH NULL AS E'\\0'; 173 174COPY testnull FROM stdin WITH NULL AS E'\\0'; 17542 \\0 176\0 \0 177\. 178 179SELECT * FROM testnull; 180 181BEGIN; 182CREATE TABLE vistest (LIKE testeoc); 183COPY vistest FROM stdin CSV; 184a0 185b 186\. 187COMMIT; 188SELECT * FROM vistest; 189BEGIN; 190TRUNCATE vistest; 191COPY vistest FROM stdin CSV; 192a1 193b 194\. 195SELECT * FROM vistest; 196SAVEPOINT s1; 197TRUNCATE vistest; 198COPY vistest FROM stdin CSV; 199d1 200e 201\. 202SELECT * FROM vistest; 203COMMIT; 204SELECT * FROM vistest; 205 206BEGIN; 207TRUNCATE vistest; 208COPY vistest FROM stdin CSV FREEZE; 209a2 210b 211\. 212SELECT * FROM vistest; 213SAVEPOINT s1; 214TRUNCATE vistest; 215COPY vistest FROM stdin CSV FREEZE; 216d2 217e 218\. 219SELECT * FROM vistest; 220COMMIT; 221SELECT * FROM vistest; 222 223BEGIN; 224TRUNCATE vistest; 225COPY vistest FROM stdin CSV FREEZE; 226x 227y 228\. 229SELECT * FROM vistest; 230COMMIT; 231TRUNCATE vistest; 232COPY vistest FROM stdin CSV FREEZE; 233p 234g 235\. 236BEGIN; 237TRUNCATE vistest; 238SAVEPOINT s1; 239COPY vistest FROM stdin CSV FREEZE; 240m 241k 242\. 243COMMIT; 244BEGIN; 245INSERT INTO vistest VALUES ('z'); 246SAVEPOINT s1; 247TRUNCATE vistest; 248ROLLBACK TO SAVEPOINT s1; 249COPY vistest FROM stdin CSV FREEZE; 250d3 251e 252\. 253COMMIT; 254CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS 255$$ 256BEGIN 257 TRUNCATE vistest; 258EXCEPTION 259 WHEN OTHERS THEN 260 INSERT INTO vistest VALUES ('subxact failure'); 261END; 262$$ language plpgsql; 263BEGIN; 264INSERT INTO vistest VALUES ('z'); 265SELECT truncate_in_subxact(); 266COPY vistest FROM stdin CSV FREEZE; 267d4 268e 269\. 270SELECT * FROM vistest; 271COMMIT; 272SELECT * FROM vistest; 273-- Test FORCE_NOT_NULL and FORCE_NULL options 274CREATE TEMP TABLE forcetest ( 275 a INT NOT NULL, 276 b TEXT NOT NULL, 277 c TEXT, 278 d TEXT, 279 e TEXT 280); 281\pset null NULL 282-- should succeed with no effect ("b" remains an empty string, "c" remains NULL) 283BEGIN; 284COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 2851,,"" 286\. 287COMMIT; 288SELECT b, c FROM forcetest WHERE a = 1; 289-- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified 290BEGIN; 291COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d)); 2922,'a',,"" 293\. 294COMMIT; 295SELECT c, d FROM forcetest WHERE a = 2; 296-- should fail with not-null constraint violation 297BEGIN; 298COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b), FORCE_NOT_NULL(c)); 2993,,"" 300\. 301ROLLBACK; 302-- should fail with "not referenced by COPY" error 303BEGIN; 304COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b)); 305ROLLBACK; 306-- should fail with "not referenced by COPY" error 307BEGIN; 308COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b)); 309ROLLBACK; 310\pset null '' 311 312-- test case with whole-row Var in a check constraint 313create table check_con_tbl (f1 int); 314create function check_con_function(check_con_tbl) returns bool as $$ 315begin 316 raise notice 'input = %', row_to_json($1); 317 return $1.f1 > 0; 318end $$ language plpgsql immutable; 319alter table check_con_tbl add check (check_con_function(check_con_tbl.*)); 320\d+ check_con_tbl 321copy check_con_tbl from stdin; 3221 323\N 324\. 325copy check_con_tbl from stdin; 3260 327\. 328select * from check_con_tbl; 329 330-- test with RLS enabled. 331CREATE ROLE regress_rls_copy_user; 332CREATE ROLE regress_rls_copy_user_colperms; 333CREATE TABLE rls_t1 (a int, b int, c int); 334 335COPY rls_t1 (a, b, c) from stdin; 3361 4 1 3372 3 2 3383 2 3 3394 1 4 340\. 341 342CREATE POLICY p1 ON rls_t1 FOR SELECT USING (a % 2 = 0); 343ALTER TABLE rls_t1 ENABLE ROW LEVEL SECURITY; 344ALTER TABLE rls_t1 FORCE ROW LEVEL SECURITY; 345 346GRANT SELECT ON TABLE rls_t1 TO regress_rls_copy_user; 347GRANT SELECT (a, b) ON TABLE rls_t1 TO regress_rls_copy_user_colperms; 348 349-- all columns 350COPY rls_t1 TO stdout; 351COPY rls_t1 (a, b, c) TO stdout; 352 353-- subset of columns 354COPY rls_t1 (a) TO stdout; 355COPY rls_t1 (a, b) TO stdout; 356 357-- column reordering 358COPY rls_t1 (b, a) TO stdout; 359 360SET SESSION AUTHORIZATION regress_rls_copy_user; 361 362-- all columns 363COPY rls_t1 TO stdout; 364COPY rls_t1 (a, b, c) TO stdout; 365 366-- subset of columns 367COPY rls_t1 (a) TO stdout; 368COPY rls_t1 (a, b) TO stdout; 369 370-- column reordering 371COPY rls_t1 (b, a) TO stdout; 372 373RESET SESSION AUTHORIZATION; 374 375SET SESSION AUTHORIZATION regress_rls_copy_user_colperms; 376 377-- attempt all columns (should fail) 378COPY rls_t1 TO stdout; 379COPY rls_t1 (a, b, c) TO stdout; 380 381-- try to copy column with no privileges (should fail) 382COPY rls_t1 (c) TO stdout; 383 384-- subset of columns (should succeed) 385COPY rls_t1 (a) TO stdout; 386COPY rls_t1 (a, b) TO stdout; 387 388RESET SESSION AUTHORIZATION; 389 390DROP TABLE forcetest; 391DROP TABLE vistest; 392DROP FUNCTION truncate_in_subxact(); 393DROP TABLE x, y; 394DROP TABLE rls_t1 CASCADE; 395DROP ROLE regress_rls_copy_user; 396DROP ROLE regress_rls_copy_user_colperms; 397DROP FUNCTION fn_x_before(); 398DROP FUNCTION fn_x_after(); 399