4
c text not null default 'stuff',
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 '
11
NEW.e := ''before trigger fired''::text;
15
CREATE FUNCTION fn_x_after () RETURNS TRIGGER AS '
17
UPDATE x set e=''after trigger fired'' where c=''stuff'';
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
37
ERROR: invalid input syntax for integer: ""
38
CONTEXT: COPY x, line 1, column a: ""
40
ERROR: missing data for column "e"
41
CONTEXT: COPY x, line 1: "2000 230 23 23"
43
ERROR: missing data for column "e"
44
CONTEXT: COPY x, line 1: "2001 231 \N \N"
45
-- extra data: should fail
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 '\\X';
53
-- check results of copy in
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
84
-- COPY w/ oids on a table w/o oids should fail
85
CREATE TABLE no_oids (
89
INSERT INTO no_oids (a, b) VALUES (5, 10);
90
INSERT INTO no_oids (a, b) VALUES (20, 30);
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
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
179
INSERT INTO y VALUES ('Jackson, Sam', '\\h');
180
INSERT INTO y VALUES ('It is "perfect".','\t');
181
INSERT INTO y VALUES ('', NULL);
182
COPY y TO stdout WITH CSV;
184
"It is ""perfect"".",
186
COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
190
COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE '\\';
192
"It is \"perfect\"."," "
195
DROP FUNCTION fn_x_before();
196
DROP FUNCTION fn_x_after();