| blob | 64ea33aeae8fdb7fd6ce09deaadc3569921e6238 |
1 CREATE TEMP TABLE x (
2 a serial,
3 b int,
4 c text not null default 'stuff',
5 d text,
6 e text
7 ) ;
8 CREATE FUNCTION fn_x_before () RETURNS TRIGGER AS '
9 BEGIN
10 NEW.e := ''before trigger fired''::text;
11 return NEW;
12 END;
13 ' LANGUAGE plpgsql;
14 CREATE 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;
20 CREATE TRIGGER trg_x_after AFTER INSERT ON x
21 FOR EACH ROW EXECUTE PROCEDURE fn_x_after();
22 CREATE TRIGGER trg_x_before BEFORE INSERT ON x
23 FOR EACH ROW EXECUTE PROCEDURE fn_x_before();
24 COPY x (a, b, c, d, e) from stdin;
25 COPY x (b, d) from stdin;
26 COPY x (b, d) from stdin;
27 COPY x (a, b, c, d, e) from stdin;
28 -- non-existent column in column list: should fail
29 COPY x (xyz) from stdin;
30 ERROR: column "xyz" of relation "x" does not exist
31 -- redundant options
32 COPY x from stdin (format CSV, FORMAT CSV);
33 ERROR: conflicting or redundant options
34 LINE 1: COPY x from stdin (format CSV, FORMAT CSV);
35 ^
36 COPY x from stdin (freeze off, freeze on);
37 ERROR: conflicting or redundant options
38 LINE 1: COPY x from stdin (freeze off, freeze on);
39 ^
40 COPY x from stdin (delimiter ',', delimiter ',');
41 ERROR: conflicting or redundant options
42 LINE 1: COPY x from stdin (delimiter ',', delimiter ',');
43 ^
44 COPY x from stdin (null ' ', null ' ');
45 ERROR: conflicting or redundant options
46 LINE 1: COPY x from stdin (null ' ', null ' ');
47 ^
48 COPY x from stdin (header off, header on);
49 ERROR: conflicting or redundant options
50 LINE 1: COPY x from stdin (header off, header on);
51 ^
52 COPY x from stdin (quote ':', quote ':');
53 ERROR: conflicting or redundant options
54 LINE 1: COPY x from stdin (quote ':', quote ':');
55 ^
56 COPY x from stdin (escape ':', escape ':');
57 ERROR: conflicting or redundant options
58 LINE 1: COPY x from stdin (escape ':', escape ':');
59 ^
60 COPY x from stdin (force_quote (a), force_quote *);
61 ERROR: conflicting or redundant options
62 LINE 1: COPY x from stdin (force_quote (a), force_quote *);
63 ^
64 COPY x from stdin (force_not_null (a), force_not_null (b));
65 ERROR: conflicting or redundant options
66 LINE 1: COPY x from stdin (force_not_null (a), force_not_null (b));
67 ^
68 COPY x from stdin (force_null (a), force_null (b));
69 ERROR: conflicting or redundant options
70 LINE 1: COPY x from stdin (force_null (a), force_null (b));
71 ^
72 COPY x from stdin (convert_selectively (a), convert_selectively (b));
73 ERROR: conflicting or redundant options
74 LINE 1: COPY x from stdin (convert_selectively (a), convert_selectiv...
75 ^
76 COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
77 ERROR: conflicting or redundant options
78 LINE 1: COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii...
79 ^
80 COPY x from stdin (on_error ignore, on_error ignore);
81 ERROR: conflicting or redundant options
82 LINE 1: COPY x from stdin (on_error ignore, on_error ignore);
83 ^
84 COPY x from stdin (log_verbosity default, log_verbosity verbose);
85 ERROR: conflicting or redundant options
86 LINE 1: COPY x from stdin (log_verbosity default, log_verbosity verb...
87 ^
88 -- incorrect options
89 COPY x from stdin (format BINARY, delimiter ',');
90 ERROR: cannot specify DELIMITER in BINARY mode
91 COPY x from stdin (format BINARY, null 'x');
92 ERROR: cannot specify NULL in BINARY mode
93 COPY x from stdin (format BINARY, on_error ignore);
94 ERROR: only ON_ERROR STOP is allowed in BINARY mode
95 COPY x from stdin (on_error unsupported);
96 ERROR: COPY ON_ERROR "unsupported" not recognized
97 LINE 1: COPY x from stdin (on_error unsupported);
98 ^
99 COPY x from stdin (format TEXT, force_quote(a));
100 ERROR: COPY FORCE_QUOTE requires CSV mode
101 COPY x from stdin (format TEXT, force_quote *);
102 ERROR: COPY FORCE_QUOTE requires CSV mode
103 COPY x from stdin (format CSV, force_quote(a));
104 ERROR: COPY FORCE_QUOTE cannot be used with COPY FROM
105 COPY x from stdin (format CSV, force_quote *);
106 ERROR: COPY FORCE_QUOTE cannot be used with COPY FROM
107 COPY x from stdin (format TEXT, force_not_null(a));
108 ERROR: COPY FORCE_NOT_NULL requires CSV mode
109 COPY x from stdin (format TEXT, force_not_null *);
110 ERROR: COPY FORCE_NOT_NULL requires CSV mode
111 COPY x to stdout (format CSV, force_not_null(a));
112 ERROR: COPY FORCE_NOT_NULL cannot be used with COPY TO
113 COPY x to stdout (format CSV, force_not_null *);
114 ERROR: COPY FORCE_NOT_NULL cannot be used with COPY TO
115 COPY x from stdin (format TEXT, force_null(a));
116 ERROR: COPY FORCE_NULL requires CSV mode
117 COPY x from stdin (format TEXT, force_null *);
118 ERROR: COPY FORCE_NULL requires CSV mode
119 COPY x to stdout (format CSV, force_null(a));
120 ERROR: COPY FORCE_NULL cannot be used with COPY TO
121 COPY x to stdout (format CSV, force_null *);
122 ERROR: COPY FORCE_NULL cannot be used with COPY TO
123 COPY x to stdout (format BINARY, on_error unsupported);
124 ERROR: COPY ON_ERROR cannot be used with COPY TO
125 LINE 1: COPY x to stdout (format BINARY, on_error unsupported);
126 ^
127 COPY x from stdin (log_verbosity unsupported);
128 ERROR: COPY LOG_VERBOSITY "unsupported" not recognized
129 LINE 1: COPY x from stdin (log_verbosity unsupported);
130 ^
131 COPY x from stdin with (reject_limit 1);
132 ERROR: COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
133 COPY x from stdin with (on_error ignore, reject_limit 0);
134 ERROR: REJECT_LIMIT (0) must be greater than zero
135 -- too many columns in column list: should fail
136 COPY x (a, b, c, d, e, d, c) from stdin;
137 ERROR: column "d" specified more than once
138 -- missing data: should fail
139 COPY x from stdin;
140 ERROR: invalid input syntax for type integer: ""
141 CONTEXT: COPY x, line 1, column a: ""
142 COPY x from stdin;
143 ERROR: missing data for column "e"
144 CONTEXT: COPY x, line 1: "2000 230 23 23"
145 COPY x from stdin;
146 ERROR: missing data for column "e"
147 CONTEXT: COPY x, line 1: "2001 231 \N \N"
148 -- extra data: should fail
149 COPY x from stdin;
150 ERROR: extra data after last expected column
151 CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80"
152 -- various COPY options: delimiters, oids, NULL string, encoding
153 COPY x (b, c, d, e) from stdin delimiter ',' null 'x';
154 COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
155 COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii';
156 COPY x TO stdout WHERE a = 1;
157 ERROR: WHERE clause not allowed with COPY TO
158 LINE 1: COPY x TO stdout WHERE a = 1;
159 ^
160 COPY x from stdin WHERE a = 50004;
161 COPY x from stdin WHERE a > 60003;
162 COPY x from stdin WHERE f > 60003;
163 ERROR: column "f" does not exist
164 LINE 1: COPY x from stdin WHERE f > 60003;
165 ^
166 COPY x from stdin WHERE a = max(x.b);
167 ERROR: aggregate functions are not allowed in COPY FROM WHERE conditions
168 LINE 1: COPY x from stdin WHERE a = max(x.b);
169 ^
170 COPY x from stdin WHERE a IN (SELECT 1 FROM x);
171 ERROR: cannot use subquery in COPY FROM WHERE condition
172 LINE 1: COPY x from stdin WHERE a IN (SELECT 1 FROM x);
173 ^
174 COPY x from stdin WHERE a IN (generate_series(1,5));
175 ERROR: set-returning functions are not allowed in COPY FROM WHERE conditions
176 LINE 1: COPY x from stdin WHERE a IN (generate_series(1,5));
177 ^
178 COPY x from stdin WHERE a = row_number() over(b);
179 ERROR: window functions are not allowed in COPY FROM WHERE conditions
180 LINE 1: COPY x from stdin WHERE a = row_number() over(b);
181 ^
182 -- check results of copy in
183 SELECT * FROM x;
184 a | b | c | d | e
185 -------+----+------------+--------+----------------------
186 9999 | | \N | NN | before trigger fired
187 10000 | 21 | 31 | 41 | before trigger fired
188 10001 | 22 | 32 | 42 | before trigger fired
189 10002 | 23 | 33 | 43 | before trigger fired
190 10003 | 24 | 34 | 44 | before trigger fired
191 10004 | 25 | 35 | 45 | before trigger fired
192 10005 | 26 | 36 | 46 | before trigger fired
193 6 | | 45 | 80 | before trigger fired
194 7 | | x | \x | before trigger fired
195 8 | | , | \, | before trigger fired
196 3000 | | c | | before trigger fired
197 4000 | | C | | before trigger fired
198 4001 | 1 | empty | | before trigger fired
199 4002 | 2 | null | | before trigger fired
200 4003 | 3 | Backslash | \ | before trigger fired
201 4004 | 4 | BackslashX | \X | before trigger fired
202 4005 | 5 | N | N | before trigger fired
203 4006 | 6 | BackslashN | \N | before trigger fired
204 4007 | 7 | XX | XX | before trigger fired
205 4008 | 8 | Delimiter | : | before trigger fired
206 50004 | 25 | 35 | 45 | before trigger fired
207 60004 | 25 | 35 | 45 | before trigger fired
208 60005 | 26 | 36 | 46 | before trigger fired
209 1 | 1 | stuff | test_1 | after trigger fired
210 2 | 2 | stuff | test_2 | after trigger fired
211 3 | 3 | stuff | test_3 | after trigger fired
212 4 | 4 | stuff | test_4 | after trigger fired
213 5 | 5 | stuff | test_5 | after trigger fired
214 (28 rows)
216 -- check copy out
217 COPY x TO stdout;
218 9999 \N \\N NN before trigger fired
219 10000 21 31 41 before trigger fired
220 10001 22 32 42 before trigger fired
221 10002 23 33 43 before trigger fired
222 10003 24 34 44 before trigger fired
223 10004 25 35 45 before trigger fired
224 10005 26 36 46 before trigger fired
225 6 \N 45 80 before trigger fired
226 7 \N x \\x before trigger fired
227 8 \N , \\, before trigger fired
228 3000 \N c \N before trigger fired
229 4000 \N C \N before trigger fired
230 4001 1 empty before trigger fired
231 4002 2 null \N before trigger fired
232 4003 3 Backslash \\ before trigger fired
233 4004 4 BackslashX \\X before trigger fired
234 4005 5 N N before trigger fired
235 4006 6 BackslashN \\N before trigger fired
236 4007 7 XX XX before trigger fired
237 4008 8 Delimiter : before trigger fired
238 50004 25 35 45 before trigger fired
239 60004 25 35 45 before trigger fired
240 60005 26 36 46 before trigger fired
241 1 1 stuff test_1 after trigger fired
242 2 2 stuff test_2 after trigger fired
243 3 3 stuff test_3 after trigger fired
244 4 4 stuff test_4 after trigger fired
245 5 5 stuff test_5 after trigger fired
246 COPY x (c, e) TO stdout;
247 \\N before trigger fired
248 31 before trigger fired
249 32 before trigger fired
250 33 before trigger fired
251 34 before trigger fired
252 35 before trigger fired
253 36 before trigger fired
254 45 before trigger fired
255 x before trigger fired
256 , before trigger fired
257 c before trigger fired
258 C before trigger fired
259 empty before trigger fired
260 null before trigger fired
261 Backslash before trigger fired
262 BackslashX before trigger fired
263 N before trigger fired
264 BackslashN before trigger fired
265 XX before trigger fired
266 Delimiter before trigger fired
267 35 before trigger fired
268 35 before trigger fired
269 36 before trigger fired
270 stuff after trigger fired
271 stuff after trigger fired
272 stuff after trigger fired
273 stuff after trigger fired
274 stuff after trigger fired
275 COPY x (b, e) TO stdout WITH NULL 'I''m null';
276 I'm null before trigger fired
277 21 before trigger fired
278 22 before trigger fired
279 23 before trigger fired
280 24 before trigger fired
281 25 before trigger fired
282 26 before trigger fired
283 I'm null before trigger fired
284 I'm null before trigger fired
285 I'm null before trigger fired
286 I'm null before trigger fired
287 I'm null before trigger fired
288 1 before trigger fired
289 2 before trigger fired
290 3 before trigger fired
291 4 before trigger fired
292 5 before trigger fired
293 6 before trigger fired
294 7 before trigger fired
295 8 before trigger fired
296 25 before trigger fired
297 25 before trigger fired
298 26 before trigger fired
299 1 after trigger fired
300 2 after trigger fired
301 3 after trigger fired
302 4 after trigger fired
303 5 after trigger fired
304 CREATE TEMP TABLE y (
305 col1 text,
306 col2 text
307 );
308 INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
309 INSERT INTO y VALUES ('It is "perfect".',E'\t');
310 INSERT INTO y VALUES ('', NULL);
311 COPY y TO stdout WITH CSV;
312 "Jackson, Sam",\h
313 "It is ""perfect"".",
314 "",
315 COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
316 Jackson, Sam|\h
317 It is "perfect".|
318 ''|
319 COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\' ENCODING 'sql_ascii';
320 "Jackson, Sam","\\h"
321 "It is \"perfect\"."," "
322 "",
323 COPY y TO stdout WITH CSV FORCE QUOTE *;
324 "Jackson, Sam","\h"
325 "It is ""perfect""."," "
326 "",
327 -- Repeat above tests with new 9.0 option syntax
328 COPY y TO stdout (FORMAT CSV);
329 "Jackson, Sam",\h
330 "It is ""perfect"".",
331 "",
332 COPY y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|');
333 Jackson, Sam|\h
334 It is "perfect".|
335 ''|
336 COPY y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\');
337 "Jackson, Sam","\\h"
338 "It is \"perfect\"."," "
339 "",
340 COPY y TO stdout (FORMAT CSV, FORCE_QUOTE *);
341 "Jackson, Sam","\h"
342 "It is ""perfect""."," "
343 "",
344 \copy y TO stdout (FORMAT CSV)
345 "Jackson, Sam",\h
346 "It is ""perfect"".",
347 "",
348 \copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|')
349 Jackson, Sam|\h
350 It is "perfect".|
351 ''|
352 \copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\')
353 "Jackson, Sam","\\h"
354 "It is \"perfect\"."," "
355 "",
356 \copy y TO stdout (FORMAT CSV, FORCE_QUOTE *)
357 "Jackson, Sam","\h"
358 "It is ""perfect""."," "
359 "",
360 --test that we read consecutive LFs properly
361 CREATE TEMP TABLE testnl (a int, b text, c int);
362 COPY testnl FROM stdin CSV;
363 -- test end of copy marker
364 CREATE TEMP TABLE testeoc (a text);
365 COPY testeoc FROM stdin CSV;
366 COPY testeoc TO stdout CSV;
367 a\.
368 \.b
369 c\.d
370 "\."
371 -- test handling of nonstandard null marker that violates escaping rules
372 CREATE TEMP TABLE testnull(a int, b text);
373 INSERT INTO testnull VALUES (1, E'\\0'), (NULL, NULL);
374 COPY testnull TO stdout WITH NULL AS E'\\0';
375 1 \\0
376 \0 \0
377 COPY testnull FROM stdin WITH NULL AS E'\\0';
378 SELECT * FROM testnull;
379 a | b
380 ----+----
381 1 | \0
382 |
383 42 | \0
384 |
385 (4 rows)
387 BEGIN;
388 CREATE TABLE vistest (LIKE testeoc);
389 COPY vistest FROM stdin CSV;
390 COMMIT;
391 SELECT * FROM vistest;
392 a
393 ----
394 a0
395 b
396 (2 rows)
398 BEGIN;
399 TRUNCATE vistest;
400 COPY vistest FROM stdin CSV;
401 SELECT * FROM vistest;
402 a
403 ----
404 a1
405 b
406 (2 rows)
408 SAVEPOINT s1;
409 TRUNCATE vistest;
410 COPY vistest FROM stdin CSV;
411 SELECT * FROM vistest;
412 a
413 ----
414 d1
415 e
416 (2 rows)
418 COMMIT;
419 SELECT * FROM vistest;
420 a
421 ----
422 d1
423 e
424 (2 rows)
426 BEGIN;
427 TRUNCATE vistest;
428 COPY vistest FROM stdin CSV FREEZE;
429 SELECT * FROM vistest;
430 a
431 ----
432 a2
433 b
434 (2 rows)
436 SAVEPOINT s1;
437 TRUNCATE vistest;
438 COPY vistest FROM stdin CSV FREEZE;
439 SELECT * FROM vistest;
440 a
441 ----
442 d2
443 e
444 (2 rows)
446 COMMIT;
447 SELECT * FROM vistest;
448 a
449 ----
450 d2
451 e
452 (2 rows)
454 BEGIN;
455 TRUNCATE vistest;
456 COPY vistest FROM stdin CSV FREEZE;
457 SELECT * FROM vistest;
458 a
459 ---
460 x
461 y
462 (2 rows)
464 COMMIT;
465 TRUNCATE vistest;
466 COPY vistest FROM stdin CSV FREEZE;
467 ERROR: cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction
468 BEGIN;
469 TRUNCATE vistest;
470 SAVEPOINT s1;
471 COPY vistest FROM stdin CSV FREEZE;
472 ERROR: cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction
473 COMMIT;
474 BEGIN;
475 INSERT INTO vistest VALUES ('z');
476 SAVEPOINT s1;
477 TRUNCATE vistest;
478 ROLLBACK TO SAVEPOINT s1;
479 COPY vistest FROM stdin CSV FREEZE;
480 ERROR: cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction
481 COMMIT;
482 CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS
483 $$
484 BEGIN
485 TRUNCATE vistest;
486 EXCEPTION
487 WHEN OTHERS THEN
488 INSERT INTO vistest VALUES ('subxact failure');
489 END;
490 $$ language plpgsql;
491 BEGIN;
492 INSERT INTO vistest VALUES ('z');
493 SELECT truncate_in_subxact();
494 truncate_in_subxact
495 ---------------------
497 (1 row)
499 COPY vistest FROM stdin CSV FREEZE;
500 SELECT * FROM vistest;
501 a
502 ----
503 d4
504 e
505 (2 rows)
507 COMMIT;
508 SELECT * FROM vistest;
509 a
510 ----
511 d4
512 e
513 (2 rows)
515 -- Test FORCE_NOT_NULL and FORCE_NULL options
516 CREATE TEMP TABLE forcetest (
517 a INT NOT NULL,
518 b TEXT NOT NULL,
519 c TEXT,
520 d TEXT,
521 e TEXT
522 );
523 \pset null NULL
524 -- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
525 BEGIN;
526 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
527 COMMIT;
528 SELECT b, c FROM forcetest WHERE a = 1;
529 b | c
530 ---+------
531 | NULL
532 (1 row)
534 -- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified
535 BEGIN;
536 COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d));
537 COMMIT;
538 SELECT c, d FROM forcetest WHERE a = 2;
539 c | d
540 ---+------
541 | NULL
542 (1 row)
544 -- should fail with not-null constraint violation
545 BEGIN;
546 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b), FORCE_NOT_NULL(c));
547 ERROR: null value in column "b" of relation "forcetest" violates not-null constraint
548 DETAIL: Failing row contains (3, null, , null, null).
549 CONTEXT: COPY forcetest, line 1: "3,,"""
550 ROLLBACK;
551 -- should fail with "not referenced by COPY" error
552 BEGIN;
553 COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b));
554 ERROR: FORCE_NOT_NULL column "b" not referenced by COPY
555 ROLLBACK;
556 -- should fail with "not referenced by COPY" error
557 BEGIN;
558 COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b));
559 ERROR: FORCE_NULL column "b" not referenced by COPY
560 ROLLBACK;
561 -- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
562 BEGIN;
563 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *, FORCE_NULL *);
564 COMMIT;
565 SELECT b, c FROM forcetest WHERE a = 4;
566 b | c
567 ---+------
568 | NULL
569 (1 row)
571 -- should succeed with effect ("b" remains an empty string)
572 BEGIN;
573 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *);
574 COMMIT;
575 SELECT b, c FROM forcetest WHERE a = 5;
576 b | c
577 ---+---
578 |
579 (1 row)
581 -- should succeed with effect ("c" remains NULL)
582 BEGIN;
583 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *);
584 COMMIT;
585 SELECT b, c FROM forcetest WHERE a = 6;
586 b | c
587 ---+------
588 b | NULL
589 (1 row)
591 -- should fail with "conflicting or redundant options" error
592 BEGIN;
593 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *, FORCE_NOT_NULL(b));
594 ERROR: conflicting or redundant options
595 LINE 1: ...c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *, FORCE_NOT_...
596 ^
597 ROLLBACK;
598 -- should fail with "conflicting or redundant options" error
599 BEGIN;
600 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL(b));
601 ERROR: conflicting or redundant options
602 LINE 1: ... b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL...
603 ^
604 ROLLBACK;
605 \pset null ''
606 -- test case with whole-row Var in a check constraint
607 create table check_con_tbl (f1 int);
608 create function check_con_function(check_con_tbl) returns bool as $$
609 begin
610 raise notice 'input = %', row_to_json($1);
611 return $1.f1 > 0;
612 end $$ language plpgsql immutable;
613 alter table check_con_tbl add check (check_con_function(check_con_tbl.*));
614 \d+ check_con_tbl
615 Table "public.check_con_tbl"
616 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
617 --------+---------+-----------+----------+---------+---------+--------------+-------------
618 f1 | integer | | | | plain | |
619 Check constraints:
620 "check_con_tbl_check" CHECK (check_con_function(check_con_tbl.*))
622 copy check_con_tbl from stdin;
623 NOTICE: input = {"f1":1}
624 NOTICE: input = {"f1":null}
625 copy check_con_tbl from stdin;
626 NOTICE: input = {"f1":0}
627 ERROR: new row for relation "check_con_tbl" violates check constraint "check_con_tbl_check"
628 DETAIL: Failing row contains (0).
629 CONTEXT: COPY check_con_tbl, line 1: "0"
630 select * from check_con_tbl;
631 f1
632 ----
633 1
635 (2 rows)
637 -- test with RLS enabled.
638 CREATE ROLE regress_rls_copy_user;
639 CREATE ROLE regress_rls_copy_user_colperms;
640 CREATE TABLE rls_t1 (a int, b int, c int);
641 COPY rls_t1 (a, b, c) from stdin;
642 CREATE POLICY p1 ON rls_t1 FOR SELECT USING (a % 2 = 0);
643 ALTER TABLE rls_t1 ENABLE ROW LEVEL SECURITY;
644 ALTER TABLE rls_t1 FORCE ROW LEVEL SECURITY;
645 GRANT SELECT ON TABLE rls_t1 TO regress_rls_copy_user;
646 GRANT SELECT (a, b) ON TABLE rls_t1 TO regress_rls_copy_user_colperms;
647 -- all columns
648 COPY rls_t1 TO stdout;
649 1 4 1
650 2 3 2
651 3 2 3
652 4 1 4
653 COPY rls_t1 (a, b, c) TO stdout;
654 1 4 1
655 2 3 2
656 3 2 3
657 4 1 4
658 -- subset of columns
659 COPY rls_t1 (a) TO stdout;
660 1
661 2
662 3
663 4
664 COPY rls_t1 (a, b) TO stdout;
665 1 4
666 2 3
667 3 2
668 4 1
669 -- column reordering
670 COPY rls_t1 (b, a) TO stdout;
671 4 1
672 3 2
673 2 3
674 1 4
675 SET SESSION AUTHORIZATION regress_rls_copy_user;
676 -- all columns
677 COPY rls_t1 TO stdout;
678 2 3 2
679 4 1 4
680 COPY rls_t1 (a, b, c) TO stdout;
681 2 3 2
682 4 1 4
683 -- subset of columns
684 COPY rls_t1 (a) TO stdout;
685 2
686 4
687 COPY rls_t1 (a, b) TO stdout;
688 2 3
689 4 1
690 -- column reordering
691 COPY rls_t1 (b, a) TO stdout;
692 3 2
693 1 4
694 RESET SESSION AUTHORIZATION;
695 SET SESSION AUTHORIZATION regress_rls_copy_user_colperms;
696 -- attempt all columns (should fail)
697 COPY rls_t1 TO stdout;
698 ERROR: permission denied for table rls_t1
699 COPY rls_t1 (a, b, c) TO stdout;
700 ERROR: permission denied for table rls_t1
701 -- try to copy column with no privileges (should fail)
702 COPY rls_t1 (c) TO stdout;
703 ERROR: permission denied for table rls_t1
704 -- subset of columns (should succeed)
705 COPY rls_t1 (a) TO stdout;
706 2
707 4
708 COPY rls_t1 (a, b) TO stdout;
709 2 3
710 4 1
711 RESET SESSION AUTHORIZATION;
712 -- test with INSTEAD OF INSERT trigger on a view
713 CREATE TABLE instead_of_insert_tbl(id serial, name text);
714 CREATE VIEW instead_of_insert_tbl_view AS SELECT ''::text AS str;
715 COPY instead_of_insert_tbl_view FROM stdin; -- fail
716 ERROR: cannot copy to view "instead_of_insert_tbl_view"
717 HINT: To enable copying to a view, provide an INSTEAD OF INSERT trigger.
718 CREATE FUNCTION fun_instead_of_insert_tbl() RETURNS trigger AS $$
719 BEGIN
720 INSERT INTO instead_of_insert_tbl (name) VALUES (NEW.str);
721 RETURN NULL;
722 END;
723 $$ LANGUAGE plpgsql;
724 CREATE TRIGGER trig_instead_of_insert_tbl_view
725 INSTEAD OF INSERT ON instead_of_insert_tbl_view
726 FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl();
727 COPY instead_of_insert_tbl_view FROM stdin;
728 SELECT * FROM instead_of_insert_tbl;
729 id | name
730 ----+-------
731 1 | test1
732 (1 row)
734 -- Test of COPY optimization with view using INSTEAD OF INSERT
735 -- trigger when relation is created in the same transaction as
736 -- when COPY is executed.
737 BEGIN;
738 CREATE VIEW instead_of_insert_tbl_view_2 as select ''::text as str;
739 CREATE TRIGGER trig_instead_of_insert_tbl_view_2
740 INSTEAD OF INSERT ON instead_of_insert_tbl_view_2
741 FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl();
742 COPY instead_of_insert_tbl_view_2 FROM stdin;
743 SELECT * FROM instead_of_insert_tbl;
744 id | name
745 ----+-------
746 1 | test1
747 2 | test1
748 (2 rows)
750 COMMIT;
751 -- tests for on_error option
752 CREATE TABLE check_ign_err (n int, m int[], k int);
753 COPY check_ign_err FROM STDIN WITH (on_error stop);
754 ERROR: invalid input syntax for type integer: "a"
755 CONTEXT: COPY check_ign_err, line 2, column n: "a"
756 -- want context for notices
757 \set SHOW_CONTEXT always
758 COPY check_ign_err FROM STDIN WITH (on_error ignore, log_verbosity verbose);
759 NOTICE: skipping row due to data type incompatibility at line 2 for column "n": "a"
760 CONTEXT: COPY check_ign_err
761 NOTICE: skipping row due to data type incompatibility at line 3 for column "k": "3333333333"
762 CONTEXT: COPY check_ign_err
763 NOTICE: skipping row due to data type incompatibility at line 4 for column "m": "{a, 4}"
764 CONTEXT: COPY check_ign_err
765 NOTICE: skipping row due to data type incompatibility at line 5 for column "n": ""
766 CONTEXT: COPY check_ign_err
767 NOTICE: skipping row due to data type incompatibility at line 7 for column "m": "a"
768 CONTEXT: COPY check_ign_err
769 NOTICE: skipping row due to data type incompatibility at line 8 for column "k": "a"
770 CONTEXT: COPY check_ign_err
771 NOTICE: 6 rows were skipped due to data type incompatibility
772 -- tests for on_error option with log_verbosity and null constraint via domain
773 CREATE DOMAIN dcheck_ign_err2 varchar(15) NOT NULL;
774 CREATE TABLE check_ign_err2 (n int, m int[], k int, l dcheck_ign_err2);
775 COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity verbose);
776 NOTICE: skipping row due to data type incompatibility at line 2 for column "l": null input
777 CONTEXT: COPY check_ign_err2
778 NOTICE: 1 row was skipped due to data type incompatibility
779 COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity silent);
780 -- reset context choice
781 \set SHOW_CONTEXT errors
782 SELECT * FROM check_ign_err;
783 n | m | k
784 ---+-----+---
785 1 | {1} | 1
786 5 | {5} | 5
787 8 | {8} | 8
788 (3 rows)
790 SELECT * FROM check_ign_err2;
791 n | m | k | l
792 ---+-----+---+-------
793 1 | {1} | 1 | 'foo'
794 3 | {3} | 3 | 'bar'
795 (2 rows)
797 -- test datatype error that can't be handled as soft: should fail
798 CREATE TABLE hard_err(foo widget);
799 COPY hard_err FROM STDIN WITH (on_error ignore);
800 ERROR: invalid input syntax for type widget: "1"
801 CONTEXT: COPY hard_err, line 1, column foo: "1"
802 -- test missing data: should fail
803 COPY check_ign_err FROM STDIN WITH (on_error ignore);
804 ERROR: missing data for column "k"
805 CONTEXT: COPY check_ign_err, line 1: "1 {1}"
806 -- test extra data: should fail
807 COPY check_ign_err FROM STDIN WITH (on_error ignore);
808 ERROR: extra data after last expected column
809 CONTEXT: COPY check_ign_err, line 1: "1 {1} 3 abc"
810 -- tests for reject_limit option
811 COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 3);
812 ERROR: skipped more than REJECT_LIMIT (3) rows due to data type incompatibility
813 CONTEXT: COPY check_ign_err, line 5, column n: ""
814 COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 4);
815 NOTICE: 4 rows were skipped due to data type incompatibility
816 -- clean up
817 DROP TABLE forcetest;
818 DROP TABLE vistest;
819 DROP FUNCTION truncate_in_subxact();
820 DROP TABLE x, y;
821 DROP TABLE rls_t1 CASCADE;
822 DROP ROLE regress_rls_copy_user;
823 DROP ROLE regress_rls_copy_user_colperms;
824 DROP FUNCTION fn_x_before();
825 DROP FUNCTION fn_x_after();
826 DROP TABLE instead_of_insert_tbl;
827 DROP VIEW instead_of_insert_tbl_view;
828 DROP VIEW instead_of_insert_tbl_view_2;
829 DROP FUNCTION fun_instead_of_insert_tbl();
830 DROP TABLE check_ign_err;
831 DROP TABLE check_ign_err2;
832 DROP DOMAIN dcheck_ign_err2;
833 DROP TABLE hard_err;
834 --
835 -- COPY FROM ... DEFAULT
836 --
837 create temp table copy_default (
838 id integer primary key,
839 text_value text not null default 'test',
840 ts_value timestamp without time zone not null default '2022-07-05'
841 );
842 -- if DEFAULT is not specified, then the marker will be regular data
843 copy copy_default from stdin;
844 select id, text_value, ts_value from copy_default;
845 id | text_value | ts_value
846 ----+------------+--------------------------
847 1 | value | Mon Jul 04 00:00:00 2022
848 2 | D | Tue Jul 05 00:00:00 2022
849 (2 rows)
851 truncate copy_default;
852 copy copy_default from stdin with (format csv);
853 select id, text_value, ts_value from copy_default;
854 id | text_value | ts_value
855 ----+------------+--------------------------
856 1 | value | Mon Jul 04 00:00:00 2022
857 2 | \D | Tue Jul 05 00:00:00 2022
858 (2 rows)
860 truncate copy_default;
861 -- DEFAULT cannot be used in binary mode
862 copy copy_default from stdin with (format binary, default '\D');
863 ERROR: cannot specify DEFAULT in BINARY mode
864 -- DEFAULT cannot be new line nor carriage return
865 copy copy_default from stdin with (default E'\n');
866 ERROR: COPY default representation cannot use newline or carriage return
867 copy copy_default from stdin with (default E'\r');
868 ERROR: COPY default representation cannot use newline or carriage return
869 -- DELIMITER cannot appear in DEFAULT spec
870 copy copy_default from stdin with (delimiter ';', default 'test;test');
871 ERROR: COPY delimiter character must not appear in the DEFAULT specification
872 -- CSV quote cannot appear in DEFAULT spec
873 copy copy_default from stdin with (format csv, quote '"', default 'test"test');
874 ERROR: CSV quote character must not appear in the DEFAULT specification
875 -- NULL and DEFAULT spec must be different
876 copy copy_default from stdin with (default '\N');
877 ERROR: NULL specification and DEFAULT specification cannot be the same
878 -- cannot use DEFAULT marker in column that has no DEFAULT value
879 copy copy_default from stdin with (default '\D');
880 ERROR: unexpected default marker in COPY data
881 DETAIL: Column "id" has no default value.
882 CONTEXT: COPY copy_default, line 1: "\D value '2022-07-04'"
883 copy copy_default from stdin with (format csv, default '\D');
884 ERROR: unexpected default marker in COPY data
885 DETAIL: Column "id" has no default value.
886 CONTEXT: COPY copy_default, line 1: "\D,value,2022-07-04"
887 -- The DEFAULT marker must be unquoted and unescaped or it's not recognized
888 copy copy_default from stdin with (default '\D');
889 select id, text_value, ts_value from copy_default;
890 id | text_value | ts_value
891 ----+------------+--------------------------
892 1 | test | Mon Jul 04 00:00:00 2022
893 2 | \D | Mon Jul 04 00:00:00 2022
894 3 | "D" | Mon Jul 04 00:00:00 2022
895 (3 rows)
897 truncate copy_default;
898 copy copy_default from stdin with (format csv, default '\D');
899 select id, text_value, ts_value from copy_default;
900 id | text_value | ts_value
901 ----+------------+--------------------------
902 1 | test | Mon Jul 04 00:00:00 2022
903 2 | \\D | Mon Jul 04 00:00:00 2022
904 3 | \D | Mon Jul 04 00:00:00 2022
905 (3 rows)
907 truncate copy_default;
908 -- successful usage of DEFAULT option in COPY
909 copy copy_default from stdin with (default '\D');
910 select id, text_value, ts_value from copy_default;
911 id | text_value | ts_value
912 ----+------------+--------------------------
913 1 | value | Mon Jul 04 00:00:00 2022
914 2 | test | Sun Jul 03 00:00:00 2022
915 3 | test | Tue Jul 05 00:00:00 2022
916 (3 rows)
918 truncate copy_default;
919 copy copy_default from stdin with (format csv, default '\D');
920 select id, text_value, ts_value from copy_default;
921 id | text_value | ts_value
922 ----+------------+--------------------------
923 1 | value | Mon Jul 04 00:00:00 2022
924 2 | test | Sun Jul 03 00:00:00 2022
925 3 | test | Tue Jul 05 00:00:00 2022
926 (3 rows)
928 truncate copy_default;
929 -- DEFAULT cannot be used in COPY TO
930 copy (select 1 as test) TO stdout with (default '\D');
931 ERROR: COPY DEFAULT cannot be used with COPY TO