1--
2-- COPY
3--
4
5-- CLASS POPULATION
6--	(any resemblance to real life is purely coincidental)
7--
8COPY aggtest FROM '@abs_srcdir@/data/agg.data';
9
10COPY onek FROM '@abs_srcdir@/data/onek.data';
11
12COPY onek TO '@abs_builddir@/results/onek.data';
13
14DELETE FROM onek;
15
16COPY onek FROM '@abs_builddir@/results/onek.data';
17
18COPY tenk1 FROM '@abs_srcdir@/data/tenk.data';
19
20COPY slow_emp4000 FROM '@abs_srcdir@/data/rect.data';
21
22COPY person FROM '@abs_srcdir@/data/person.data';
23
24COPY emp FROM '@abs_srcdir@/data/emp.data';
25
26COPY student FROM '@abs_srcdir@/data/student.data';
27
28COPY stud_emp FROM '@abs_srcdir@/data/stud_emp.data';
29
30COPY road FROM '@abs_srcdir@/data/streets.data';
31
32COPY real_city FROM '@abs_srcdir@/data/real_city.data';
33
34COPY hash_i4_heap FROM '@abs_srcdir@/data/hash.data';
35
36COPY hash_name_heap FROM '@abs_srcdir@/data/hash.data';
37
38COPY hash_txt_heap FROM '@abs_srcdir@/data/hash.data';
39
40COPY hash_f8_heap FROM '@abs_srcdir@/data/hash.data';
41
42COPY test_tsvector FROM '@abs_srcdir@/data/tsearch.data';
43
44COPY testjsonb FROM '@abs_srcdir@/data/jsonb.data';
45
46-- the data in this file has a lot of duplicates in the index key
47-- fields, leading to long bucket chains and lots of table expansion.
48-- this is therefore a stress test of the bucket overflow code (unlike
49-- the data in hash.data, which has unique index keys).
50--
51-- COPY hash_ovfl_heap FROM '@abs_srcdir@/data/hashovfl.data';
52
53COPY bt_i4_heap FROM '@abs_srcdir@/data/desc.data';
54
55COPY bt_name_heap FROM '@abs_srcdir@/data/hash.data';
56
57COPY bt_txt_heap FROM '@abs_srcdir@/data/desc.data';
58
59COPY bt_f8_heap FROM '@abs_srcdir@/data/hash.data';
60
61COPY array_op_test FROM '@abs_srcdir@/data/array.data';
62
63COPY array_index_op_test FROM '@abs_srcdir@/data/array.data';
64
65-- analyze all the data we just loaded, to ensure plan consistency
66-- in later tests
67
68ANALYZE aggtest;
69ANALYZE onek;
70ANALYZE tenk1;
71ANALYZE slow_emp4000;
72ANALYZE person;
73ANALYZE emp;
74ANALYZE student;
75ANALYZE stud_emp;
76ANALYZE road;
77ANALYZE real_city;
78ANALYZE hash_i4_heap;
79ANALYZE hash_name_heap;
80ANALYZE hash_txt_heap;
81ANALYZE hash_f8_heap;
82ANALYZE test_tsvector;
83ANALYZE bt_i4_heap;
84ANALYZE bt_name_heap;
85ANALYZE bt_txt_heap;
86ANALYZE bt_f8_heap;
87ANALYZE array_op_test;
88ANALYZE array_index_op_test;
89
90--- test copying in CSV mode with various styles
91--- of embedded line ending characters
92
93create temp table copytest (
94	style	text,
95	test 	text,
96	filler	int);
97
98insert into copytest values('DOS',E'abc\r\ndef',1);
99insert into copytest values('Unix',E'abc\ndef',2);
100insert into copytest values('Mac',E'abc\rdef',3);
101insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
102
103copy copytest to '@abs_builddir@/results/copytest.csv' csv;
104
105create temp table copytest2 (like copytest);
106
107copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
108
109select * from copytest except select * from copytest2;
110
111truncate copytest2;
112
113--- same test but with an escape char different from quote char
114
115copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
116
117copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
118
119select * from copytest except select * from copytest2;
120
121
122-- test header line feature
123
124create temp table copytest3 (
125	c1 int,
126	"col with , comma" text,
127	"col with "" quote"  int);
128
129copy copytest3 from stdin csv header;
130this is just a line full of junk that would error out if parsed
1311,a,1
1322,b,2
133\.
134
135copy copytest3 to stdout csv header;
136
137-- test copy from with a partitioned table
138create table parted_copytest (
139	a int,
140	b int,
141	c text
142) partition by list (b);
143
144create table parted_copytest_a1 (c text, b int, a int);
145create table parted_copytest_a2 (a int, c text, b int);
146
147alter table parted_copytest attach partition parted_copytest_a1 for values in(1);
148alter table parted_copytest attach partition parted_copytest_a2 for values in(2);
149
150-- We must insert enough rows to trigger multi-inserts.  These are only
151-- enabled adaptively when there are few enough partition changes.
152insert into parted_copytest select x,1,'One' from generate_series(1,1000) x;
153insert into parted_copytest select x,2,'Two' from generate_series(1001,1010) x;
154insert into parted_copytest select x,1,'One' from generate_series(1011,1020) x;
155
156copy (select * from parted_copytest order by a) to '@abs_builddir@/results/parted_copytest.csv';
157
158-- Ensure COPY FREEZE errors for partitioned tables.
159begin;
160truncate parted_copytest;
161copy parted_copytest from '@abs_builddir@/results/parted_copytest.csv' (freeze);
162rollback;
163
164drop table parted_copytest;
165