1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
CREATE 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;
9999 \N \\N \NN \N
10000 21 31 41 51
\.
COPY x (b, d) from stdin;
1 test_1
\.
COPY x (b, d) from stdin;
2 test_2
3 test_3
4 test_4
5 test_5
\.
COPY x (a, b, c, d, e) from stdin;
10001 22 32 42 52
10002 23 33 43 53
10003 24 34 44 54
10004 25 35 45 55
10005 26 36 46 56
\.
-- non-existent column in column list: should fail
COPY x (xyz) from stdin;
-- too many columns in column list: should fail
COPY x (a, b, c, d, e, d, c) from stdin;
-- missing data: should fail
COPY x from stdin;
\.
COPY x from stdin;
2000 230 23 23
\.
COPY x from stdin;
2001 231 \N \N
\.
-- extra data: should fail
COPY x from stdin;
2002 232 40 50 60 70 80
\.
-- various COPY options: delimiters, oids, NULL string
COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
500000,x,45,80,90
500001,x,\x,\\x,\\\x
500002,x,\,,\\\,,\\
\.
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
3000;;c;;
\.
COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
4000:\X:C:\X:\X
4001:1:empty::
4002:2:null:\X:\X
4003:3:Backslash:\\:\\
4004:4:BackslashX:\\X:\\X
4005:5:N:\N:\N
4006:6:BackslashN:\\N:\\N
4007:7:XX:\XX:\XX
4008:8:Delimiter:\::\:
\.
-- check results of copy in
SELECT * FROM x;
-- COPY w/ oids on a table w/o oids should fail
CREATE TABLE no_oids (
a int,
b int
) WITHOUT OIDS;
INSERT INTO no_oids (a, b) VALUES (5, 10);
INSERT INTO no_oids (a, b) VALUES (20, 30);
-- should fail
COPY no_oids FROM stdin WITH OIDS;
COPY no_oids TO stdout WITH OIDS;
-- check copy out
COPY x TO stdout;
COPY x (c, e) TO stdout;
COPY x (b, e) TO stdout WITH NULL 'I''m null';
DROP TABLE x;
DROP FUNCTION fn_x_before();
DROP FUNCTION fn_x_after();
|
