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
|
-- Test basic TRUNCATE functionality.
CREATE TABLE truncate_a (col1 integer primary key);
INSERT INTO truncate_a VALUES (1);
INSERT INTO truncate_a VALUES (2);
SELECT * FROM truncate_a;
-- Roll truncate back
BEGIN;
TRUNCATE truncate_a;
ROLLBACK;
SELECT * FROM truncate_a;
-- Commit the truncate this time
BEGIN;
TRUNCATE truncate_a;
COMMIT;
SELECT * FROM truncate_a;
-- Test foreign-key checks
CREATE TABLE trunc_b (a int REFERENCES truncate_a);
CREATE TABLE trunc_c (a serial PRIMARY KEY);
CREATE TABLE trunc_d (a int REFERENCES trunc_c);
CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c);
TRUNCATE TABLE truncate_a; -- fail
TRUNCATE TABLE truncate_a,trunc_b; -- fail
TRUNCATE TABLE truncate_a,trunc_b,trunc_e; -- ok
TRUNCATE TABLE truncate_a,trunc_e; -- fail
TRUNCATE TABLE trunc_c; -- fail
TRUNCATE TABLE trunc_c,trunc_d; -- fail
TRUNCATE TABLE trunc_c,trunc_d,trunc_e; -- ok
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a; -- fail
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok
TRUNCATE TABLE truncate_a RESTRICT; -- fail
TRUNCATE TABLE truncate_a CASCADE; -- ok
-- circular references
ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
-- Add some data to verify that truncating actually works ...
INSERT INTO trunc_c VALUES (1);
INSERT INTO truncate_a VALUES (1);
INSERT INTO trunc_b VALUES (1);
INSERT INTO trunc_d VALUES (1);
INSERT INTO trunc_e VALUES (1,1);
TRUNCATE TABLE trunc_c;
TRUNCATE TABLE trunc_c,truncate_a;
TRUNCATE TABLE trunc_c,truncate_a,trunc_d;
TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e;
TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e,trunc_b;
-- Verify that truncating did actually work
SELECT * FROM truncate_a
UNION ALL
SELECT * FROM trunc_c
UNION ALL
SELECT * FROM trunc_b
UNION ALL
SELECT * FROM trunc_d;
SELECT * FROM trunc_e;
-- Add data again to test TRUNCATE ... CASCADE
INSERT INTO trunc_c VALUES (1);
INSERT INTO truncate_a VALUES (1);
INSERT INTO trunc_b VALUES (1);
INSERT INTO trunc_d VALUES (1);
INSERT INTO trunc_e VALUES (1,1);
TRUNCATE TABLE trunc_c CASCADE; -- ok
SELECT * FROM truncate_a
UNION ALL
SELECT * FROM trunc_c
UNION ALL
SELECT * FROM trunc_b
UNION ALL
SELECT * FROM trunc_d;
SELECT * FROM trunc_e;
DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
|
