1-- 2-- Test cases for COPY (select) TO 3-- 4create table test1 (id serial, t text); 5insert into test1 (t) values ('a'); 6insert into test1 (t) values ('b'); 7insert into test1 (t) values ('c'); 8insert into test1 (t) values ('d'); 9insert into test1 (t) values ('e'); 10create table test2 (id serial, t text); 11insert into test2 (t) values ('A'); 12insert into test2 (t) values ('B'); 13insert into test2 (t) values ('C'); 14insert into test2 (t) values ('D'); 15insert into test2 (t) values ('E'); 16create view v_test1 17as select 'v_'||t from test1; 18-- 19-- Test COPY table TO 20-- 21copy test1 to stdout; 221 a 232 b 243 c 254 d 265 e 27-- 28-- This should fail 29-- 30copy v_test1 to stdout; 31ERROR: cannot copy from view "v_test1" 32HINT: Try the COPY (SELECT ...) TO variant. 33-- 34-- Test COPY (select) TO 35-- 36copy (select t from test1 where id=1) to stdout; 37a 38-- 39-- Test COPY (select for update) TO 40-- 41copy (select t from test1 where id=3 for update) to stdout; 42c 43-- 44-- This should fail 45-- 46copy (select t into temp test3 from test1 where id=3) to stdout; 47ERROR: COPY (SELECT INTO) is not supported 48-- 49-- This should fail 50-- 51copy (select * from test1) from stdin; 52ERROR: syntax error at or near "from" 53LINE 1: copy (select * from test1) from stdin; 54 ^ 55-- 56-- This should fail 57-- 58copy (select * from test1) (t,id) to stdout; 59ERROR: syntax error at or near "(" 60LINE 1: copy (select * from test1) (t,id) to stdout; 61 ^ 62-- 63-- Test JOIN 64-- 65copy (select * from test1 join test2 using (id)) to stdout; 661 a A 672 b B 683 c C 694 d D 705 e E 71-- 72-- Test UNION SELECT 73-- 74copy (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) to stdout; 75a 76v_a 77v_b 78v_c 79v_d 80v_e 81-- 82-- Test subselect 83-- 84copy (select * from (select t from test1 where id = 1 UNION select * from v_test1 ORDER BY 1) t1) to stdout; 85a 86v_a 87v_b 88v_c 89v_d 90v_e 91-- 92-- Test headers, CSV and quotes 93-- 94copy (select t from test1 where id = 1) to stdout csv header force quote t; 95t 96"a" 97-- 98-- Test psql builtins, plain table 99-- 100\copy test1 to stdout 1011 a 1022 b 1033 c 1044 d 1055 e 106-- 107-- This should fail 108-- 109\copy v_test1 to stdout 110ERROR: cannot copy from view "v_test1" 111HINT: Try the COPY (SELECT ...) TO variant. 112-- 113-- Test \copy (select ...) 114-- 115\copy (select "id",'id','id""'||t,(id + 1)*id,t,"test1"."t" from test1 where id=3) to stdout 1163 id id""c 12 c c 117-- 118-- Drop everything 119-- 120drop table test2; 121drop view v_test1; 122drop table test1; 123-- psql handling of COPY in multi-command strings 124copy (select 1) to stdout\; select 1/0; -- row, then error 1251 126ERROR: division by zero 127select 1/0\; copy (select 1) to stdout; -- error only 128ERROR: division by zero 129copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; -- 1 2 3 1301 1312 132 ?column? 133---------- 134 3 135(1 row) 136 137create table test3 (c int); 138select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1 139 ?column? 140---------- 141 1 142(1 row) 143 144select * from test3; 145 c 146--- 147 1 148 2 149(2 rows) 150 151drop table test3; 152