blob: 8a42b0e3d807e6b99eecbb4ef8588df8650c357f (
plain)
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
|
--
-- Test cases for COPY (select) TO
--
create table test1 (id serial, t text);
NOTICE: CREATE TABLE will create implicit sequence "test1_id_seq" for serial column "test1.id"
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);
NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for serial column "test2.id"
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.
\copy: 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;
|
