1CREATE TEMP TABLE x ( 2 a serial, 3 b int, 4 c text not null default 'stuff', 5 d text, 6 e text 7) ; 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 delimiter ',' null 'x'; 77x,45,80,90 78x,\x,\\x,\\\x 79x,\,,\\\,,\\ 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 98COPY x TO stdout WHERE a = 1; 99COPY x from stdin WHERE a = 50004; 10050003 24 34 44 54 10150004 25 35 45 55 10250005 26 36 46 56 103\. 104 105COPY x from stdin WHERE a > 60003; 10660001 22 32 42 52 10760002 23 33 43 53 10860003 24 34 44 54 10960004 25 35 45 55 11060005 26 36 46 56 111\. 112 113COPY x from stdin WHERE f > 60003; 114 115COPY x from stdin WHERE a = max(x.b); 116 117COPY x from stdin WHERE a IN (SELECT 1 FROM x); 118 119COPY x from stdin WHERE a IN (generate_series(1,5)); 120 121COPY x from stdin WHERE a = row_number() over(b); 122 123 124-- check results of copy in 125SELECT * FROM x; 126 127-- check copy out 128COPY x TO stdout; 129COPY x (c, e) TO stdout; 130COPY x (b, e) TO stdout WITH NULL 'I''m null'; 131 132CREATE TEMP TABLE y ( 133 col1 text, 134 col2 text 135); 136 137INSERT INTO y VALUES ('Jackson, Sam', E'\\h'); 138INSERT INTO y VALUES ('It is "perfect".',E'\t'); 139INSERT INTO y VALUES ('', NULL); 140 141COPY y TO stdout WITH CSV; 142COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|'; 143COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\' ENCODING 'sql_ascii'; 144COPY y TO stdout WITH CSV FORCE QUOTE *; 145 146-- Repeat above tests with new 9.0 option syntax 147 148COPY y TO stdout (FORMAT CSV); 149COPY y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|'); 150COPY y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\'); 151COPY y TO stdout (FORMAT CSV, FORCE_QUOTE *); 152 153\copy y TO stdout (FORMAT CSV) 154\copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|') 155\copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\') 156\copy y TO stdout (FORMAT CSV, FORCE_QUOTE *) 157 158--test that we read consecutive LFs properly 159 160CREATE TEMP TABLE testnl (a int, b text, c int); 161 162COPY testnl FROM stdin CSV; 1631,"a field with two LFs 164 165inside",2 166\. 167 168-- test end of copy marker 169CREATE TEMP TABLE testeoc (a text); 170 171COPY testeoc FROM stdin CSV; 172a\. 173\.b 174c\.d 175"\." 176\. 177 178COPY testeoc TO stdout CSV; 179 180-- test handling of nonstandard null marker that violates escaping rules 181 182CREATE TEMP TABLE testnull(a int, b text); 183INSERT INTO testnull VALUES (1, E'\\0'), (NULL, NULL); 184 185COPY testnull TO stdout WITH NULL AS E'\\0'; 186 187COPY testnull FROM stdin WITH NULL AS E'\\0'; 18842 \\0 189\0 \0 190\. 191 192SELECT * FROM testnull; 193 194BEGIN; 195CREATE TABLE vistest (LIKE testeoc); 196COPY vistest FROM stdin CSV; 197a0 198b 199\. 200COMMIT; 201SELECT * FROM vistest; 202BEGIN; 203TRUNCATE vistest; 204COPY vistest FROM stdin CSV; 205a1 206b 207\. 208SELECT * FROM vistest; 209SAVEPOINT s1; 210TRUNCATE vistest; 211COPY vistest FROM stdin CSV; 212d1 213e 214\. 215SELECT * FROM vistest; 216COMMIT; 217SELECT * FROM vistest; 218 219BEGIN; 220TRUNCATE vistest; 221COPY vistest FROM stdin CSV FREEZE; 222a2 223b 224\. 225SELECT * FROM vistest; 226SAVEPOINT s1; 227TRUNCATE vistest; 228COPY vistest FROM stdin CSV FREEZE; 229d2 230e 231\. 232SELECT * FROM vistest; 233COMMIT; 234SELECT * FROM vistest; 235 236BEGIN; 237TRUNCATE vistest; 238COPY vistest FROM stdin CSV FREEZE; 239x 240y 241\. 242SELECT * FROM vistest; 243COMMIT; 244TRUNCATE vistest; 245COPY vistest FROM stdin CSV FREEZE; 246p 247g 248\. 249BEGIN; 250TRUNCATE vistest; 251SAVEPOINT s1; 252COPY vistest FROM stdin CSV FREEZE; 253m 254k 255\. 256COMMIT; 257BEGIN; 258INSERT INTO vistest VALUES ('z'); 259SAVEPOINT s1; 260TRUNCATE vistest; 261ROLLBACK TO SAVEPOINT s1; 262COPY vistest FROM stdin CSV FREEZE; 263d3 264e 265\. 266COMMIT; 267CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS 268$$ 269BEGIN 270 TRUNCATE vistest; 271EXCEPTION 272 WHEN OTHERS THEN 273 INSERT INTO vistest VALUES ('subxact failure'); 274END; 275$$ language plpgsql; 276BEGIN; 277INSERT INTO vistest VALUES ('z'); 278SELECT truncate_in_subxact(); 279COPY vistest FROM stdin CSV FREEZE; 280d4 281e 282\. 283SELECT * FROM vistest; 284COMMIT; 285SELECT * FROM vistest; 286-- Test FORCE_NOT_NULL and FORCE_NULL options 287CREATE TEMP TABLE forcetest ( 288 a INT NOT NULL, 289 b TEXT NOT NULL, 290 c TEXT, 291 d TEXT, 292 e TEXT 293); 294\pset null NULL 295-- should succeed with no effect ("b" remains an empty string, "c" remains NULL) 296BEGIN; 297COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 2981,,"" 299\. 300COMMIT; 301SELECT b, c FROM forcetest WHERE a = 1; 302-- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified 303BEGIN; 304COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d)); 3052,'a',,"" 306\. 307COMMIT; 308SELECT c, d FROM forcetest WHERE a = 2; 309-- should fail with not-null constraint violation 310BEGIN; 311COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b), FORCE_NOT_NULL(c)); 3123,,"" 313\. 314ROLLBACK; 315-- should fail with "not referenced by COPY" error 316BEGIN; 317COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b)); 318ROLLBACK; 319-- should fail with "not referenced by COPY" error 320BEGIN; 321COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b)); 322ROLLBACK; 323\pset null '' 324 325-- test case with whole-row Var in a check constraint 326create table check_con_tbl (f1 int); 327create function check_con_function(check_con_tbl) returns bool as $$ 328begin 329 raise notice 'input = %', row_to_json($1); 330 return $1.f1 > 0; 331end $$ language plpgsql immutable; 332alter table check_con_tbl add check (check_con_function(check_con_tbl.*)); 333\d+ check_con_tbl 334copy check_con_tbl from stdin; 3351 336\N 337\. 338copy check_con_tbl from stdin; 3390 340\. 341select * from check_con_tbl; 342 343-- test with RLS enabled. 344CREATE ROLE regress_rls_copy_user; 345CREATE ROLE regress_rls_copy_user_colperms; 346CREATE TABLE rls_t1 (a int, b int, c int); 347 348COPY rls_t1 (a, b, c) from stdin; 3491 4 1 3502 3 2 3513 2 3 3524 1 4 353\. 354 355CREATE POLICY p1 ON rls_t1 FOR SELECT USING (a % 2 = 0); 356ALTER TABLE rls_t1 ENABLE ROW LEVEL SECURITY; 357ALTER TABLE rls_t1 FORCE ROW LEVEL SECURITY; 358 359GRANT SELECT ON TABLE rls_t1 TO regress_rls_copy_user; 360GRANT SELECT (a, b) ON TABLE rls_t1 TO regress_rls_copy_user_colperms; 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 373SET SESSION AUTHORIZATION regress_rls_copy_user; 374 375-- all columns 376COPY rls_t1 TO stdout; 377COPY rls_t1 (a, b, c) TO stdout; 378 379-- subset of columns 380COPY rls_t1 (a) TO stdout; 381COPY rls_t1 (a, b) TO stdout; 382 383-- column reordering 384COPY rls_t1 (b, a) TO stdout; 385 386RESET SESSION AUTHORIZATION; 387 388SET SESSION AUTHORIZATION regress_rls_copy_user_colperms; 389 390-- attempt all columns (should fail) 391COPY rls_t1 TO stdout; 392COPY rls_t1 (a, b, c) TO stdout; 393 394-- try to copy column with no privileges (should fail) 395COPY rls_t1 (c) TO stdout; 396 397-- subset of columns (should succeed) 398COPY rls_t1 (a) TO stdout; 399COPY rls_t1 (a, b) TO stdout; 400 401RESET SESSION AUTHORIZATION; 402 403-- test with INSTEAD OF INSERT trigger on a view 404CREATE TABLE instead_of_insert_tbl(id serial, name text); 405CREATE VIEW instead_of_insert_tbl_view AS SELECT ''::text AS str; 406 407COPY instead_of_insert_tbl_view FROM stdin; -- fail 408test1 409\. 410 411CREATE FUNCTION fun_instead_of_insert_tbl() RETURNS trigger AS $$ 412BEGIN 413 INSERT INTO instead_of_insert_tbl (name) VALUES (NEW.str); 414 RETURN NULL; 415END; 416$$ LANGUAGE plpgsql; 417CREATE TRIGGER trig_instead_of_insert_tbl_view 418 INSTEAD OF INSERT ON instead_of_insert_tbl_view 419 FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl(); 420 421COPY instead_of_insert_tbl_view FROM stdin; 422test1 423\. 424 425SELECT * FROM instead_of_insert_tbl; 426 427-- Test of COPY optimization with view using INSTEAD OF INSERT 428-- trigger when relation is created in the same transaction as 429-- when COPY is executed. 430BEGIN; 431CREATE VIEW instead_of_insert_tbl_view_2 as select ''::text as str; 432CREATE TRIGGER trig_instead_of_insert_tbl_view_2 433 INSTEAD OF INSERT ON instead_of_insert_tbl_view_2 434 FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl(); 435 436COPY instead_of_insert_tbl_view_2 FROM stdin; 437test1 438\. 439 440SELECT * FROM instead_of_insert_tbl; 441COMMIT; 442 443-- clean up 444DROP TABLE forcetest; 445DROP TABLE vistest; 446DROP FUNCTION truncate_in_subxact(); 447DROP TABLE x, y; 448DROP TABLE rls_t1 CASCADE; 449DROP ROLE regress_rls_copy_user; 450DROP ROLE regress_rls_copy_user_colperms; 451DROP FUNCTION fn_x_before(); 452DROP FUNCTION fn_x_after(); 453DROP TABLE instead_of_insert_tbl; 454DROP VIEW instead_of_insert_tbl_view; 455DROP VIEW instead_of_insert_tbl_view_2; 456DROP FUNCTION fun_instead_of_insert_tbl(); 457