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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
--
-- Test cases for COPY (select) TO
--
create table test1 (id serial, t text);
insert into test1 (t) values ('a');
insert into test1 (t) values ('b');
insert into test1 (t) values ('c');
insert into test1 (t) values ('d');
insert into test1 (t) values ('e');
create table test2 (id serial, t text);
insert into test2 (t) values ('A');
insert into test2 (t) values ('B');
insert into test2 (t) values ('C');
insert into test2 (t) values ('D');
insert into test2 (t) values ('E');
create view v_test1
as select 'v_'||t from test1;
--
-- Test COPY table TO
--
copy test1 to stdout;
1 a
2 b
3 c
4 d
5 e
--
-- This should fail
--
copy v_test1 to stdout;
ERROR: cannot copy from view "v_test1"
HINT: Try the COPY (SELECT ...) TO variant.
--
-- Test COPY (select) TO
--
copy (select t from test1 where id=1) to stdout;
a
--
-- Test COPY (select for update) TO
--
copy (select t from test1 where id=3 for update) to stdout;
c
--
-- This should fail
--
copy (select t into temp test3 from test1 where id=3) to stdout;
ERROR: COPY (SELECT INTO) is not supported
--
-- This should fail
--
copy (select * from test1) from stdin;
ERROR: syntax error at or near "from"
LINE 1: copy (select * from test1) from stdin;
^
--
-- This should fail
--
copy (select * from test1) (t,id) to stdout;
ERROR: syntax error at or near "("
LINE 1: copy (select * from test1) (t,id) to stdout;
^
--
-- Test JOIN
--
copy (select * from test1 join test2 using (id)) to stdout;
1 a A
2 b B
3 c C
4 d D
5 e E
--
-- Test UNION SELECT
--
copy (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) to stdout;
a
v_a
v_b
v_c
v_d
v_e
--
-- Test subselect
--
copy (select * from (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) t1) to stdout;
a
v_a
v_b
v_c
v_d
v_e
--
-- Test headers, CSV and quotes
--
copy (select t from test1 where id = 1) to stdout csv header force quote t;
t
"a"
--
-- Test psql builtins, plain table
--
\copy test1 to stdout
1 a
2 b
3 c
4 d
5 e
--
-- This should fail
--
\copy v_test1 to stdout
ERROR: cannot copy from view "v_test1"
HINT: Try the COPY (SELECT ...) TO variant.
--
-- Test \copy (select ...)
--
\copy (select "id",'id','id""'||t,(id + 1)*id,t,"test1"."t" from test1 where id=3) to stdout
3 id id""c 12 c c
--
-- Drop everything
--
drop table test2;
drop view v_test1;
drop table test1;
-- psql handling of COPY in multi-command strings
copy (select 1) to stdout\; select 1/0; -- row, then error
1
ERROR: division by zero
select 1/0\; copy (select 1) to stdout; -- error only
ERROR: division by zero
copy (select 1) to stdout\; copy (select 2) to stdout\; select 3\; select 4; -- 1 2 3 4
1
2
?column?
----------
3
(1 row)
?column?
----------
4
(1 row)
create table test3 (c int);
select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 0 1
?column?
----------
0
(1 row)
?column?
----------
1
(1 row)
select * from test3;
c
---
1
2
(2 rows)
drop table test3;
|
