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; 8CREATE FUNCTION fn_x_before () RETURNS TRIGGER AS ' 9 BEGIN 10 NEW.e := ''before trigger fired''::text; 11 return NEW; 12 END; 13' LANGUAGE plpgsql; 14CREATE FUNCTION fn_x_after () RETURNS TRIGGER AS ' 15 BEGIN 16 UPDATE x set e=''after trigger fired'' where c=''stuff''; 17 return NULL; 18 END; 19' LANGUAGE plpgsql; 20CREATE TRIGGER trg_x_after AFTER INSERT ON x 21FOR EACH ROW EXECUTE PROCEDURE fn_x_after(); 22CREATE TRIGGER trg_x_before BEFORE INSERT ON x 23FOR EACH ROW EXECUTE PROCEDURE fn_x_before(); 24COPY x (a, b, c, d, e) from stdin; 25COPY x (b, d) from stdin; 26COPY x (b, d) from stdin; 27COPY x (a, b, c, d, e) from stdin; 28-- non-existent column in column list: should fail 29COPY x (xyz) from stdin; 30ERROR: column "xyz" of relation "x" does not exist 31-- too many columns in column list: should fail 32COPY x (a, b, c, d, e, d, c) from stdin; 33ERROR: column "d" specified more than once 34-- missing data: should fail 35COPY x from stdin; 36ERROR: invalid input syntax for integer: "" 37CONTEXT: COPY x, line 1, column a: "" 38COPY x from stdin; 39ERROR: missing data for column "e" 40CONTEXT: COPY x, line 1: "2000 230 23 23" 41COPY x from stdin; 42ERROR: missing data for column "e" 43CONTEXT: COPY x, line 1: "2001 231 \N \N" 44-- extra data: should fail 45COPY x from stdin; 46ERROR: extra data after last expected column 47CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80" 48-- various COPY options: delimiters, oids, NULL string, encoding 49COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x'; 50COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; 51COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii'; 52-- check results of copy in 53SELECT * FROM x; 54 a | b | c | d | e 55-------+----+------------+--------+---------------------- 56 9999 | | \N | NN | before trigger fired 57 10000 | 21 | 31 | 41 | before trigger fired 58 10001 | 22 | 32 | 42 | before trigger fired 59 10002 | 23 | 33 | 43 | before trigger fired 60 10003 | 24 | 34 | 44 | before trigger fired 61 10004 | 25 | 35 | 45 | before trigger fired 62 10005 | 26 | 36 | 46 | before trigger fired 63 6 | | 45 | 80 | before trigger fired 64 7 | | x | \x | before trigger fired 65 8 | | , | \, | before trigger fired 66 3000 | | c | | before trigger fired 67 4000 | | C | | before trigger fired 68 4001 | 1 | empty | | before trigger fired 69 4002 | 2 | null | | before trigger fired 70 4003 | 3 | Backslash | \ | before trigger fired 71 4004 | 4 | BackslashX | \X | before trigger fired 72 4005 | 5 | N | N | before trigger fired 73 4006 | 6 | BackslashN | \N | before trigger fired 74 4007 | 7 | XX | XX | before trigger fired 75 4008 | 8 | Delimiter | : | before trigger fired 76 1 | 1 | stuff | test_1 | after trigger fired 77 2 | 2 | stuff | test_2 | after trigger fired 78 3 | 3 | stuff | test_3 | after trigger fired 79 4 | 4 | stuff | test_4 | after trigger fired 80 5 | 5 | stuff | test_5 | after trigger fired 81(25 rows) 82 83-- COPY w/ oids on a table w/o oids should fail 84CREATE TABLE no_oids ( 85 a int, 86 b int 87) WITHOUT OIDS; 88INSERT INTO no_oids (a, b) VALUES (5, 10); 89INSERT INTO no_oids (a, b) VALUES (20, 30); 90-- should fail 91COPY no_oids FROM stdin WITH OIDS; 92ERROR: table "no_oids" does not have OIDs 93COPY no_oids TO stdout WITH OIDS; 94ERROR: table "no_oids" does not have OIDs 95-- check copy out 96COPY x TO stdout; 979999 \N \\N NN before trigger fired 9810000 21 31 41 before trigger fired 9910001 22 32 42 before trigger fired 10010002 23 33 43 before trigger fired 10110003 24 34 44 before trigger fired 10210004 25 35 45 before trigger fired 10310005 26 36 46 before trigger fired 1046 \N 45 80 before trigger fired 1057 \N x \\x before trigger fired 1068 \N , \\, before trigger fired 1073000 \N c \N before trigger fired 1084000 \N C \N before trigger fired 1094001 1 empty before trigger fired 1104002 2 null \N before trigger fired 1114003 3 Backslash \\ before trigger fired 1124004 4 BackslashX \\X before trigger fired 1134005 5 N N before trigger fired 1144006 6 BackslashN \\N before trigger fired 1154007 7 XX XX before trigger fired 1164008 8 Delimiter : before trigger fired 1171 1 stuff test_1 after trigger fired 1182 2 stuff test_2 after trigger fired 1193 3 stuff test_3 after trigger fired 1204 4 stuff test_4 after trigger fired 1215 5 stuff test_5 after trigger fired 122COPY x (c, e) TO stdout; 123\\N before trigger fired 12431 before trigger fired 12532 before trigger fired 12633 before trigger fired 12734 before trigger fired 12835 before trigger fired 12936 before trigger fired 13045 before trigger fired 131x before trigger fired 132, before trigger fired 133c before trigger fired 134C before trigger fired 135empty before trigger fired 136null before trigger fired 137Backslash before trigger fired 138BackslashX before trigger fired 139N before trigger fired 140BackslashN before trigger fired 141XX before trigger fired 142Delimiter before trigger fired 143stuff after trigger fired 144stuff after trigger fired 145stuff after trigger fired 146stuff after trigger fired 147stuff after trigger fired 148COPY x (b, e) TO stdout WITH NULL 'I''m null'; 149I'm null before trigger fired 15021 before trigger fired 15122 before trigger fired 15223 before trigger fired 15324 before trigger fired 15425 before trigger fired 15526 before trigger fired 156I'm null before trigger fired 157I'm null before trigger fired 158I'm null before trigger fired 159I'm null before trigger fired 160I'm null before trigger fired 1611 before trigger fired 1622 before trigger fired 1633 before trigger fired 1644 before trigger fired 1655 before trigger fired 1666 before trigger fired 1677 before trigger fired 1688 before trigger fired 1691 after trigger fired 1702 after trigger fired 1713 after trigger fired 1724 after trigger fired 1735 after trigger fired 174CREATE TEMP TABLE y ( 175 col1 text, 176 col2 text 177); 178INSERT INTO y VALUES ('Jackson, Sam', E'\\h'); 179INSERT INTO y VALUES ('It is "perfect".',E'\t'); 180INSERT INTO y VALUES ('', NULL); 181COPY y TO stdout WITH CSV; 182"Jackson, Sam",\h 183"It is ""perfect"".", 184"", 185COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|'; 186Jackson, Sam|\h 187It is "perfect".| 188''| 189COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\' ENCODING 'sql_ascii'; 190"Jackson, Sam","\\h" 191"It is \"perfect\"."," " 192"", 193COPY y TO stdout WITH CSV FORCE QUOTE *; 194"Jackson, Sam","\h" 195"It is ""perfect""."," " 196"", 197-- Repeat above tests with new 9.0 option syntax 198COPY y TO stdout (FORMAT CSV); 199"Jackson, Sam",\h 200"It is ""perfect"".", 201"", 202COPY y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|'); 203Jackson, Sam|\h 204It is "perfect".| 205''| 206COPY y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\'); 207"Jackson, Sam","\\h" 208"It is \"perfect\"."," " 209"", 210COPY y TO stdout (FORMAT CSV, FORCE_QUOTE *); 211"Jackson, Sam","\h" 212"It is ""perfect""."," " 213"", 214\copy y TO stdout (FORMAT CSV) 215"Jackson, Sam",\h 216"It is ""perfect"".", 217"", 218\copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|') 219Jackson, Sam|\h 220It is "perfect".| 221''| 222\copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\') 223"Jackson, Sam","\\h" 224"It is \"perfect\"."," " 225"", 226\copy y TO stdout (FORMAT CSV, FORCE_QUOTE *) 227"Jackson, Sam","\h" 228"It is ""perfect""."," " 229"", 230--test that we read consecutive LFs properly 231CREATE TEMP TABLE testnl (a int, b text, c int); 232COPY testnl FROM stdin CSV; 233-- test end of copy marker 234CREATE TEMP TABLE testeoc (a text); 235COPY testeoc FROM stdin CSV; 236COPY testeoc TO stdout CSV; 237a\. 238\.b 239c\.d 240"\." 241-- test handling of nonstandard null marker that violates escaping rules 242CREATE TEMP TABLE testnull(a int, b text); 243INSERT INTO testnull VALUES (1, E'\\0'), (NULL, NULL); 244COPY testnull TO stdout WITH NULL AS E'\\0'; 2451 \\0 246\0 \0 247COPY testnull FROM stdin WITH NULL AS E'\\0'; 248SELECT * FROM testnull; 249 a | b 250----+---- 251 1 | \0 252 | 253 42 | \0 254 | 255(4 rows) 256 257BEGIN; 258CREATE TABLE vistest (LIKE testeoc); 259COPY vistest FROM stdin CSV; 260COMMIT; 261SELECT * FROM vistest; 262 a 263---- 264 a0 265 b 266(2 rows) 267 268BEGIN; 269TRUNCATE vistest; 270COPY vistest FROM stdin CSV; 271SELECT * FROM vistest; 272 a 273---- 274 a1 275 b 276(2 rows) 277 278SAVEPOINT s1; 279TRUNCATE vistest; 280COPY vistest FROM stdin CSV; 281SELECT * FROM vistest; 282 a 283---- 284 d1 285 e 286(2 rows) 287 288COMMIT; 289SELECT * FROM vistest; 290 a 291---- 292 d1 293 e 294(2 rows) 295 296BEGIN; 297TRUNCATE vistest; 298COPY vistest FROM stdin CSV FREEZE; 299SELECT * FROM vistest; 300 a 301---- 302 a2 303 b 304(2 rows) 305 306SAVEPOINT s1; 307TRUNCATE vistest; 308COPY vistest FROM stdin CSV FREEZE; 309SELECT * FROM vistest; 310 a 311---- 312 d2 313 e 314(2 rows) 315 316COMMIT; 317SELECT * FROM vistest; 318 a 319---- 320 d2 321 e 322(2 rows) 323 324BEGIN; 325TRUNCATE vistest; 326COPY vistest FROM stdin CSV FREEZE; 327SELECT * FROM vistest; 328 a 329--- 330 x 331 y 332(2 rows) 333 334COMMIT; 335TRUNCATE vistest; 336COPY vistest FROM stdin CSV FREEZE; 337ERROR: cannot perform FREEZE because the table was not created or truncated in the current subtransaction 338BEGIN; 339TRUNCATE vistest; 340SAVEPOINT s1; 341COPY vistest FROM stdin CSV FREEZE; 342ERROR: cannot perform FREEZE because the table was not created or truncated in the current subtransaction 343COMMIT; 344BEGIN; 345INSERT INTO vistest VALUES ('z'); 346SAVEPOINT s1; 347TRUNCATE vistest; 348ROLLBACK TO SAVEPOINT s1; 349COPY vistest FROM stdin CSV FREEZE; 350ERROR: cannot perform FREEZE because the table was not created or truncated in the current subtransaction 351COMMIT; 352CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS 353$$ 354BEGIN 355 TRUNCATE vistest; 356EXCEPTION 357 WHEN OTHERS THEN 358 INSERT INTO vistest VALUES ('subxact failure'); 359END; 360$$ language plpgsql; 361BEGIN; 362INSERT INTO vistest VALUES ('z'); 363SELECT truncate_in_subxact(); 364 truncate_in_subxact 365--------------------- 366 367(1 row) 368 369COPY vistest FROM stdin CSV FREEZE; 370SELECT * FROM vistest; 371 a 372---- 373 d4 374 e 375(2 rows) 376 377COMMIT; 378SELECT * FROM vistest; 379 a 380---- 381 d4 382 e 383(2 rows) 384 385-- Test FORCE_NOT_NULL and FORCE_NULL options 386CREATE TEMP TABLE forcetest ( 387 a INT NOT NULL, 388 b TEXT NOT NULL, 389 c TEXT, 390 d TEXT, 391 e TEXT 392); 393\pset null NULL 394-- should succeed with no effect ("b" remains an empty string, "c" remains NULL) 395BEGIN; 396COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 397COMMIT; 398SELECT b, c FROM forcetest WHERE a = 1; 399 b | c 400---+------ 401 | NULL 402(1 row) 403 404-- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified 405BEGIN; 406COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d)); 407COMMIT; 408SELECT c, d FROM forcetest WHERE a = 2; 409 c | d 410---+------ 411 | NULL 412(1 row) 413 414-- should fail with not-null constraint violation 415BEGIN; 416COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b), FORCE_NOT_NULL(c)); 417ERROR: null value in column "b" violates not-null constraint 418DETAIL: Failing row contains (3, null, , null, null). 419CONTEXT: COPY forcetest, line 1: "3,,""" 420ROLLBACK; 421-- should fail with "not referenced by COPY" error 422BEGIN; 423COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b)); 424ERROR: FORCE_NOT_NULL column "b" not referenced by COPY 425ROLLBACK; 426-- should fail with "not referenced by COPY" error 427BEGIN; 428COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b)); 429ERROR: FORCE_NULL column "b" not referenced by COPY 430ROLLBACK; 431\pset null '' 432-- test case with whole-row Var in a check constraint 433create table check_con_tbl (f1 int); 434create function check_con_function(check_con_tbl) returns bool as $$ 435begin 436 raise notice 'input = %', row_to_json($1); 437 return $1.f1 > 0; 438end $$ language plpgsql immutable; 439alter table check_con_tbl add check (check_con_function(check_con_tbl.*)); 440\d+ check_con_tbl 441 Table "public.check_con_tbl" 442 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 443--------+---------+-----------+----------+---------+---------+--------------+------------- 444 f1 | integer | | | | plain | | 445Check constraints: 446 "check_con_tbl_check" CHECK (check_con_function(check_con_tbl.*)) 447 448copy check_con_tbl from stdin; 449NOTICE: input = {"f1":1} 450NOTICE: input = {"f1":null} 451copy check_con_tbl from stdin; 452NOTICE: input = {"f1":0} 453ERROR: new row for relation "check_con_tbl" violates check constraint "check_con_tbl_check" 454DETAIL: Failing row contains (0). 455CONTEXT: COPY check_con_tbl, line 1: "0" 456select * from check_con_tbl; 457 f1 458---- 459 1 460 461(2 rows) 462 463-- test with RLS enabled. 464CREATE ROLE regress_rls_copy_user; 465CREATE ROLE regress_rls_copy_user_colperms; 466CREATE TABLE rls_t1 (a int, b int, c int); 467COPY rls_t1 (a, b, c) from stdin; 468CREATE POLICY p1 ON rls_t1 FOR SELECT USING (a % 2 = 0); 469ALTER TABLE rls_t1 ENABLE ROW LEVEL SECURITY; 470ALTER TABLE rls_t1 FORCE ROW LEVEL SECURITY; 471GRANT SELECT ON TABLE rls_t1 TO regress_rls_copy_user; 472GRANT SELECT (a, b) ON TABLE rls_t1 TO regress_rls_copy_user_colperms; 473-- all columns 474COPY rls_t1 TO stdout; 4751 4 1 4762 3 2 4773 2 3 4784 1 4 479COPY rls_t1 (a, b, c) TO stdout; 4801 4 1 4812 3 2 4823 2 3 4834 1 4 484-- subset of columns 485COPY rls_t1 (a) TO stdout; 4861 4872 4883 4894 490COPY rls_t1 (a, b) TO stdout; 4911 4 4922 3 4933 2 4944 1 495-- column reordering 496COPY rls_t1 (b, a) TO stdout; 4974 1 4983 2 4992 3 5001 4 501SET SESSION AUTHORIZATION regress_rls_copy_user; 502-- all columns 503COPY rls_t1 TO stdout; 5042 3 2 5054 1 4 506COPY rls_t1 (a, b, c) TO stdout; 5072 3 2 5084 1 4 509-- subset of columns 510COPY rls_t1 (a) TO stdout; 5112 5124 513COPY rls_t1 (a, b) TO stdout; 5142 3 5154 1 516-- column reordering 517COPY rls_t1 (b, a) TO stdout; 5183 2 5191 4 520RESET SESSION AUTHORIZATION; 521SET SESSION AUTHORIZATION regress_rls_copy_user_colperms; 522-- attempt all columns (should fail) 523COPY rls_t1 TO stdout; 524ERROR: permission denied for table rls_t1 525COPY rls_t1 (a, b, c) TO stdout; 526ERROR: permission denied for table rls_t1 527-- try to copy column with no privileges (should fail) 528COPY rls_t1 (c) TO stdout; 529ERROR: permission denied for table rls_t1 530-- subset of columns (should succeed) 531COPY rls_t1 (a) TO stdout; 5322 5334 534COPY rls_t1 (a, b) TO stdout; 5352 3 5364 1 537RESET SESSION AUTHORIZATION; 538-- test with INSTEAD OF INSERT trigger on a view 539CREATE TABLE instead_of_insert_tbl(id serial, name text); 540CREATE VIEW instead_of_insert_tbl_view AS SELECT ''::text AS str; 541COPY instead_of_insert_tbl_view FROM stdin; -- fail 542ERROR: cannot copy to view "instead_of_insert_tbl_view" 543HINT: To enable copying to a view, provide an INSTEAD OF INSERT trigger. 544CREATE FUNCTION fun_instead_of_insert_tbl() RETURNS trigger AS $$ 545BEGIN 546 INSERT INTO instead_of_insert_tbl (name) VALUES (NEW.str); 547 RETURN NULL; 548END; 549$$ LANGUAGE plpgsql; 550CREATE TRIGGER trig_instead_of_insert_tbl_view 551 INSTEAD OF INSERT ON instead_of_insert_tbl_view 552 FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl(); 553COPY instead_of_insert_tbl_view FROM stdin; 554SELECT * FROM instead_of_insert_tbl; 555 id | name 556----+------- 557 1 | test1 558(1 row) 559 560-- Test of COPY optimization with view using INSTEAD OF INSERT 561-- trigger when relation is created in the same transaction as 562-- when COPY is executed. 563BEGIN; 564CREATE VIEW instead_of_insert_tbl_view_2 as select ''::text as str; 565CREATE TRIGGER trig_instead_of_insert_tbl_view_2 566 INSTEAD OF INSERT ON instead_of_insert_tbl_view_2 567 FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl(); 568COPY instead_of_insert_tbl_view_2 FROM stdin; 569SELECT * FROM instead_of_insert_tbl; 570 id | name 571----+------- 572 1 | test1 573 2 | test1 574(2 rows) 575 576COMMIT; 577-- clean up 578DROP TABLE forcetest; 579DROP TABLE vistest; 580DROP FUNCTION truncate_in_subxact(); 581DROP TABLE x, y; 582DROP TABLE rls_t1 CASCADE; 583DROP ROLE regress_rls_copy_user; 584DROP ROLE regress_rls_copy_user_colperms; 585DROP FUNCTION fn_x_before(); 586DROP FUNCTION fn_x_after(); 587DROP TABLE instead_of_insert_tbl; 588DROP VIEW instead_of_insert_tbl_view; 589DROP VIEW instead_of_insert_tbl_view_2; 590DROP FUNCTION fun_instead_of_insert_tbl(); 591