| blob | 7f374ac1a6f79e41dc530501ed85254d0db9864d |
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 ) WITH OIDS;
8 NOTICE: CREATE TABLE will create implicit sequence "x_a_seq" for serial column "x.a"
9 CREATE 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 CREATE FUNCTION fn_x_after () RETURNS TRIGGER AS '
16 BEGIN
17 UPDATE x set e=''after trigger fired'' where c=''stuff'';
18 return NULL;
19 END;
20 ' LANGUAGE plpgsql;
21 CREATE TRIGGER trg_x_after AFTER INSERT ON x
22 FOR EACH ROW EXECUTE PROCEDURE fn_x_after();
23 CREATE TRIGGER trg_x_before BEFORE INSERT ON x
24 FOR EACH ROW EXECUTE PROCEDURE fn_x_before();
25 COPY x (a, b, c, d, e) from stdin;
26 COPY x (b, d) from stdin;
27 COPY x (b, d) from stdin;
28 COPY x (a, b, c, d, e) from stdin;
29 -- non-existent column in column list: should fail
30 COPY x (xyz) from stdin;
31 ERROR: column "xyz" of relation "x" does not exist
32 -- too many columns in column list: should fail
33 COPY x (a, b, c, d, e, d, c) from stdin;
34 ERROR: column "d" specified more than once
35 -- missing data: should fail
36 COPY x from stdin;
37 ERROR: invalid input syntax for integer: ""
38 CONTEXT: COPY x, line 1, column a: ""
39 COPY x from stdin;
40 ERROR: missing data for column "e"
41 CONTEXT: COPY x, line 1: "2000 230 23 23"
42 COPY x from stdin;
43 ERROR: missing data for column "e"
44 CONTEXT: COPY x, line 1: "2001 231 \N \N"
45 -- extra data: should fail
46 COPY x from stdin;
47 ERROR: extra data after last expected column
48 CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80"
49 -- various COPY options: delimiters, oids, NULL string
50 COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
51 COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
52 COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
53 -- check results of copy in
54 SELECT * FROM x;
55 a | b | c | d | e
56 -------+----+------------+--------+----------------------
57 9999 | | \N | NN | before trigger fired
58 10000 | 21 | 31 | 41 | before trigger fired
59 10001 | 22 | 32 | 42 | before trigger fired
60 10002 | 23 | 33 | 43 | before trigger fired
61 10003 | 24 | 34 | 44 | before trigger fired
62 10004 | 25 | 35 | 45 | before trigger fired
63 10005 | 26 | 36 | 46 | before trigger fired
64 6 | | 45 | 80 | before trigger fired
65 7 | | x | \x | before trigger fired
66 8 | | , | \, | before trigger fired
67 3000 | | c | | before trigger fired
68 4000 | | C | | before trigger fired
69 4001 | 1 | empty | | before trigger fired
70 4002 | 2 | null | | before trigger fired
71 4003 | 3 | Backslash | \ | before trigger fired
72 4004 | 4 | BackslashX | \X | before trigger fired
73 4005 | 5 | N | N | before trigger fired
74 4006 | 6 | BackslashN | \N | before trigger fired
75 4007 | 7 | XX | XX | before trigger fired
76 4008 | 8 | Delimiter | : | before trigger fired
77 1 | 1 | stuff | test_1 | after trigger fired
78 2 | 2 | stuff | test_2 | after trigger fired
79 3 | 3 | stuff | test_3 | after trigger fired
80 4 | 4 | stuff | test_4 | after trigger fired
81 5 | 5 | stuff | test_5 | after trigger fired
82 (25 rows)
84 -- COPY w/ oids on a table w/o oids should fail
85 CREATE TABLE no_oids (
86 a int,
87 b int
88 ) WITHOUT OIDS;
89 INSERT INTO no_oids (a, b) VALUES (5, 10);
90 INSERT INTO no_oids (a, b) VALUES (20, 30);
91 -- should fail
92 COPY no_oids FROM stdin WITH OIDS;
93 ERROR: table "no_oids" does not have OIDs
94 COPY no_oids TO stdout WITH OIDS;
95 ERROR: table "no_oids" does not have OIDs
96 -- check copy out
97 COPY x TO stdout;
98 9999 \N \\N NN before trigger fired
99 10000 21 31 41 before trigger fired
100 10001 22 32 42 before trigger fired
101 10002 23 33 43 before trigger fired
102 10003 24 34 44 before trigger fired
103 10004 25 35 45 before trigger fired
104 10005 26 36 46 before trigger fired
105 6 \N 45 80 before trigger fired
106 7 \N x \\x before trigger fired
107 8 \N , \\, before trigger fired
108 3000 \N c \N before trigger fired
109 4000 \N C \N before trigger fired
110 4001 1 empty before trigger fired
111 4002 2 null \N before trigger fired
112 4003 3 Backslash \\ before trigger fired
113 4004 4 BackslashX \\X before trigger fired
114 4005 5 N N before trigger fired
115 4006 6 BackslashN \\N before trigger fired
116 4007 7 XX XX before trigger fired
117 4008 8 Delimiter : before trigger fired
118 1 1 stuff test_1 after trigger fired
119 2 2 stuff test_2 after trigger fired
120 3 3 stuff test_3 after trigger fired
121 4 4 stuff test_4 after trigger fired
122 5 5 stuff test_5 after trigger fired
123 COPY x (c, e) TO stdout;
124 \\N before trigger fired
125 31 before trigger fired
126 32 before trigger fired
127 33 before trigger fired
128 34 before trigger fired
129 35 before trigger fired
130 36 before trigger fired
131 45 before trigger fired
132 x before trigger fired
133 , before trigger fired
134 c before trigger fired
135 C before trigger fired
136 empty before trigger fired
137 null before trigger fired
138 Backslash before trigger fired
139 BackslashX before trigger fired
140 N before trigger fired
141 BackslashN before trigger fired
142 XX before trigger fired
143 Delimiter before trigger fired
144 stuff after trigger fired
145 stuff after trigger fired
146 stuff after trigger fired
147 stuff after trigger fired
148 stuff after trigger fired
149 COPY x (b, e) TO stdout WITH NULL 'I''m null';
150 I'm null before trigger fired
151 21 before trigger fired
152 22 before trigger fired
153 23 before trigger fired
154 24 before trigger fired
155 25 before trigger fired
156 26 before trigger fired
157 I'm null before trigger fired
158 I'm null before trigger fired
159 I'm null before trigger fired
160 I'm null before trigger fired
161 I'm null before trigger fired
162 1 before trigger fired
163 2 before trigger fired
164 3 before trigger fired
165 4 before trigger fired
166 5 before trigger fired
167 6 before trigger fired
168 7 before trigger fired
169 8 before trigger fired
170 1 after trigger fired
171 2 after trigger fired
172 3 after trigger fired
173 4 after trigger fired
174 5 after trigger fired
175 CREATE TEMP TABLE y (
176 col1 text,
177 col2 text
178 );
179 INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
180 INSERT INTO y VALUES ('It is "perfect".',E'\t');
181 INSERT INTO y VALUES ('', NULL);
182 COPY y TO stdout WITH CSV;
183 "Jackson, Sam",\h
184 "It is ""perfect"".",
185 "",
186 COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
187 Jackson, Sam|\h
188 It is "perfect".|
189 ''|
190 COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';
191 "Jackson, Sam","\\h"
192 "It is \"perfect\"."," "
193 "",
194 --test that we read consecutive LFs properly
195 CREATE TEMP TABLE testnl (a int, b text, c int);
196 COPY testnl FROM stdin CSV;
197 -- test end of copy marker
198 CREATE TEMP TABLE testeoc (a text);
199 COPY testeoc FROM stdin CSV;
200 COPY testeoc TO stdout CSV;
201 a\.
202 \.b
203 c\.d
204 "\."
205 DROP TABLE x, y;
206 DROP FUNCTION fn_x_before();
207 DROP FUNCTION fn_x_after();