1
insert into T_pkey1 values (1, 'key1-1', 'test key');
2
insert into T_pkey1 values (1, 'key1-2', 'test key');
3
insert into T_pkey1 values (1, 'key1-3', 'test key');
4
insert into T_pkey1 values (2, 'key2-1', 'test key');
5
insert into T_pkey1 values (2, 'key2-2', 'test key');
6
insert into T_pkey1 values (2, 'key2-3', 'test key');
7
insert into T_pkey2 values (1, 'key1-1', 'test key');
8
insert into T_pkey2 values (1, 'key1-2', 'test key');
9
insert into T_pkey2 values (1, 'key1-3', 'test key');
10
insert into T_pkey2 values (2, 'key2-1', 'test key');
11
insert into T_pkey2 values (2, 'key2-2', 'test key');
12
insert into T_pkey2 values (2, 'key2-3', 'test key');
13
select * from T_pkey1;
15
------+----------------------+------------------------------------------
24
select * from T_pkey2;
26
------+----------------------+------------------------------------------
35
insert into T_pkey1 values (1, 'KEY1-3', 'should work');
36
insert into T_pkey2 values (1, 'KEY1-3', 'should fail');
37
ERROR: duplicate key '1', 'KEY1-3' for T_pkey2
38
insert into T_dta1 values ('trec 1', 1, 'key1-1');
39
insert into T_dta1 values ('trec 2', 1, 'key1-2');
40
insert into T_dta1 values ('trec 3', 1, 'key1-3');
41
insert into T_dta1 values ('trec 4', 1, 'key1-4');
42
ERROR: key for t_dta1 not in t_pkey1
43
insert into T_dta2 values ('trec 1', 1, 'KEY1-1');
44
insert into T_dta2 values ('trec 2', 1, 'KEY1-2');
45
insert into T_dta2 values ('trec 3', 1, 'KEY1-3');
46
insert into T_dta2 values ('trec 4', 1, 'KEY1-4');
47
ERROR: key for t_dta2 not in t_pkey2
50
------------+------+----------------------
58
------------+------+----------------------
64
update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1';
65
update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1';
66
ERROR: key '1', 'key1-1 ' referenced by T_dta1
67
delete from T_pkey1 where key1 = 2 and key2 = 'key2-2';
68
delete from T_pkey1 where key1 = 1 and key2 = 'key1-2';
69
ERROR: key '1', 'key1-2 ' referenced by T_dta1
70
update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1';
71
update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1';
72
NOTICE: updated 1 entries in T_dta2 for new key in T_pkey2
73
delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2';
74
delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2';
75
NOTICE: deleted 1 entries from T_dta2
76
select * from T_pkey1;
78
------+----------------------+------------------------------------------
83
1 | KEY1-3 | should work
87
select * from T_pkey2;
89
------+----------------------+------------------------------------------
98
------------+------+----------------------
104
select * from T_dta2;
106
------------+------+----------------------
111
select tcl_avg(key1) from T_pkey1;
117
select tcl_sum(key1) from T_pkey1;
123
select tcl_avg(key1) from T_pkey2;
129
select tcl_sum(key1) from T_pkey2;
135
select tcl_avg(key1) from T_pkey1 where key1 = 99;
141
select tcl_sum(key1) from T_pkey1 where key1 = 99;
159
select * from T_pkey1 order by key1 using @<, key2;
161
------+----------------------+------------------------------------------
162
1 | KEY1-3 | should work
163
1 | key1-1 | test key
164
1 | key1-2 | test key
165
1 | key1-3 | test key
166
2 | key2-3 | test key
167
2 | key2-9 | test key
170
select * from T_pkey2 order by key1 using @<, key2;
172
------+----------------------+------------------------------------------
173
1 | KEY1-3 | test key
174
1 | KEY1-9 | test key
175
2 | KEY2-3 | test key
176
2 | KEY2-9 | test key