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
|
--
-- See https://github.com/dimitri/pgcopydb/issues/280
--
create schema foo;
-- create status dictionary table
create table foo.tbl_status (
id bigserial not null primary key,
name varchar(32) not null unique check (name != '')
);
insert into foo.tbl_status (id, name)
values (1, 'draft'),
(2, 'active'),
(3, 'closed');
-- fix id sequence value the manual way
SELECT setval(pg_get_serial_sequence('foo.tbl_status', 'id'),
(SELECT COALESCE(MAX(id) + 1, 1) FROM foo.tbl_status),
false);
-- create first table
create table foo.tbl1 (
id bigserial not null primary key,
status_id bigint not null default 1 references foo.tbl_status(id),
desc_text varchar(32)
);
create index if not exists tbl1_status_id_idx on foo.tbl1(status_id);
-- create second table
create table foo.tbl2 (
id bigserial not null primary key,
tbl1_id bigint not null references foo.tbl1(id),
desc_text varchar(32)
);
create index if not exists tbl2_tbl1_id_idx on foo.tbl2(tbl1_id);
--
-- And another schema that we exclude wholesale.
--
create schema bar;
--
-- See https://github.com/dimitri/pgcopydb/issues/390
--
create schema app;
create schema copy;
create table app.foo(id bigserial, f1 text);
create table copy.foo(like app.foo including all);
--
-- See https://github.com/dimitri/pgcopydb/issues/413
--
create schema schema_name_20_chars;
create table schema_name_20_chars.very______long______table______name_______50_chars
(
id serial
);
--
-- To test materialized view filtering
--
create materialized view foo.matview_1 as select 1 as id;
create index matview_1_idx on foo.matview_1(id);
create materialized view foo.matview_1_exclude_data as select 1;
create materialized view foo.matview_1_exclude_as_table as select 1 as id;
create materialized view foo.matview_2_depends_on_matview_1_exclude_as_table as select * from foo.matview_1_exclude_as_table;
--
-- TODO: We don't handle the case where a materialized view depends
-- on another materialized view that's refresh is filtered out.
-- In that case, we should exclude the materialized refresh of
-- the dependent materialized view as well.
--
-- create materialized view foo.matview_3_depends_on_matview_1_exclude_table as select * from foo.matview_1_exclude_data;
--
-- See: https://github.com/dimitri/pgcopydb/issues/817
--
create schema seq;
-- A sequence used as default
create sequence seq.default_table_id_seq;
create table seq.default_table (id integer primary key default nextval('seq.default_table_id_seq'));
select setval('seq.default_table_id_seq', 667);
-- A sequence used as identity
create table seq.identity_table (id integer primary key generated always as identity);
select setval('seq.identity_table_id_seq', 668);
-- A standalone sequence
create sequence seq.standalone_id_seq;
select setval('seq.standalone_id_seq', 669);
-- A standalone sequence smallint
create sequence seq.standalone_smallint_id_seq as smallint;
select setval('seq.standalone_smallint_id_seq', 670);
-- A standalone sequence with a minvalue that has not been set
create sequence seq.standalone_minvalue_id_seq minvalue 671;
create schema partitioned_tables;
create table partitioned_tables.sellers (
id bigint,
archive smallint not null
) partition by list (archive);
create table partitioned_tables.sellers_active partition of partitioned_tables.sellers default;
create table partitioned_tables.sellers_archive partition of partitioned_tables.sellers for values in ('1');
insert into partitioned_tables.sellers (id, archive) values (1, 0), (2, 1), (3, 0);
|
