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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
|
insert into T_pkey1 values (1, 'key1-1', 'test key');
insert into T_pkey1 values (1, 'key1-2', 'test key');
insert into T_pkey1 values (1, 'key1-3', 'test key');
insert into T_pkey1 values (2, 'key2-1', 'test key');
insert into T_pkey1 values (2, 'key2-2', 'test key');
insert into T_pkey1 values (2, 'key2-3', 'test key');
insert into T_pkey2 values (1, 'key1-1', 'test key');
insert into T_pkey2 values (1, 'key1-2', 'test key');
insert into T_pkey2 values (1, 'key1-3', 'test key');
insert into T_pkey2 values (2, 'key2-1', 'test key');
insert into T_pkey2 values (2, 'key2-2', 'test key');
insert into T_pkey2 values (2, 'key2-3', 'test key');
select * from T_pkey1;
key1 | key2 | txt
------+--------+----------
1 | key1-1 | test key
1 | key1-2 | test key
1 | key1-3 | test key
2 | key2-1 | test key
2 | key2-2 | test key
2 | key2-3 | test key
(6 rows)
select * from T_pkey2;
key1 | key2 | txt
------+--------+----------
1 | KEY1-1 | test key
1 | KEY1-2 | test key
1 | KEY1-3 | test key
2 | KEY2-1 | test key
2 | KEY2-2 | test key
2 | KEY2-3 | test key
(6 rows)
insert into T_pkey1 values (1, 'KEY1-3', 'should work');
insert into T_pkey2 values (1, 'KEY1-3', 'should fail');
ERROR: duplicate key '1', 'KEY1-3' for T_pkey2
insert into T_dta1 values ('trec 1', 1, 'key1-1');
insert into T_dta1 values ('trec 2', 1, 'key1-2');
insert into T_dta1 values ('trec 3', 1, 'key1-3');
insert into T_dta1 values ('trec 4', 1, 'key1-4');
ERROR: key for t_dta1 not in t_pkey1
insert into T_dta2 values ('trec 1', 1, 'KEY1-1');
insert into T_dta2 values ('trec 2', 1, 'KEY1-2');
insert into T_dta2 values ('trec 3', 1, 'KEY1-3');
insert into T_dta2 values ('trec 4', 1, 'KEY1-4');
ERROR: key for t_dta2 not in t_pkey2
select * from T_dta1;
tkey | ref1 | ref2
--------+------+--------
trec 1 | 1 | key1-1
trec 2 | 1 | key1-2
trec 3 | 1 | key1-3
(3 rows)
select * from T_dta2;
tkey | ref1 | ref2
--------+------+--------
trec 1 | 1 | KEY1-1
trec 2 | 1 | KEY1-2
trec 3 | 1 | KEY1-3
(3 rows)
update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1';
update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1';
ERROR: key '1', 'key1-1' referenced by T_dta1
delete from T_pkey1 where key1 = 2 and key2 = 'key2-2';
delete from T_pkey1 where key1 = 1 and key2 = 'key1-2';
ERROR: key '1', 'key1-2' referenced by T_dta1
update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1';
update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1';
NOTICE: updated 1 entries in T_dta2 for new key in T_pkey2
delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2';
delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2';
NOTICE: deleted 1 entries from T_dta2
select * from T_pkey1;
key1 | key2 | txt
------+--------+-------------
1 | key1-1 | test key
1 | key1-2 | test key
1 | key1-3 | test key
2 | key2-3 | test key
1 | KEY1-3 | should work
2 | key2-9 | test key
(6 rows)
select * from T_pkey2;
key1 | key2 | txt
------+--------+----------
1 | KEY1-3 | test key
2 | KEY2-3 | test key
2 | KEY2-9 | test key
1 | KEY1-9 | test key
(4 rows)
select * from T_dta1;
tkey | ref1 | ref2
--------+------+--------
trec 1 | 1 | key1-1
trec 2 | 1 | key1-2
trec 3 | 1 | key1-3
(3 rows)
select * from T_dta2;
tkey | ref1 | ref2
--------+------+--------
trec 3 | 1 | KEY1-3
trec 1 | 1 | KEY1-9
(2 rows)
select ruby_avg(key1) from T_pkey1;
ruby_avg
----------
1
(1 row)
select ruby_sum(key1) from T_pkey1;
ruby_sum
----------
8
(1 row)
select ruby_avg(key1) from T_pkey2;
ruby_avg
----------
1
(1 row)
select ruby_sum(key1) from T_pkey2;
ruby_sum
----------
6
(1 row)
select ruby_avg(key1) from T_pkey1 where key1 = 99;
ruby_avg
----------
(1 row)
select ruby_sum(key1) from T_pkey1 where key1 = 99;
ruby_sum
----------
0
(1 row)
select 1 @< 2;
?column?
----------
t
(1 row)
select 100 @< 4;
?column?
----------
f
(1 row)
select * from T_pkey1 order by key1 using @<;
key1 | key2 | txt
------+--------+-------------
1 | key1-1 | test key
1 | key1-2 | test key
1 | key1-3 | test key
1 | KEY1-3 | should work
2 | key2-3 | test key
2 | key2-9 | test key
(6 rows)
select * from T_pkey2 order by key1 using @<;
key1 | key2 | txt
------+--------+----------
1 | KEY1-3 | test key
1 | KEY1-9 | test key
2 | KEY2-3 | test key
2 | KEY2-9 | test key
(4 rows)
|
