1 -- Test basic TRUNCATE functionality.
2 CREATE TABLE truncate_a (col1 integer primary key);
3 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "truncate_a_pkey" for table "truncate_a"
4 INSERT INTO truncate_a VALUES (1);
5 INSERT INTO truncate_a VALUES (2);
6 SELECT * FROM truncate_a;
17 SELECT * FROM truncate_a;
24 -- Commit the truncate this time
28 SELECT * FROM truncate_a;
33 -- Test foreign-key checks
34 CREATE TABLE trunc_b (a int REFERENCES truncate_a);
35 CREATE TABLE trunc_c (a serial PRIMARY KEY);
36 NOTICE: CREATE TABLE will create implicit sequence "trunc_c_a_seq" for serial column "trunc_c.a"
37 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "trunc_c_pkey" for table "trunc_c"
38 CREATE TABLE trunc_d (a int REFERENCES trunc_c);
39 CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c);
40 TRUNCATE TABLE truncate_a; -- fail
41 ERROR: cannot truncate a table referenced in a foreign key constraint
42 DETAIL: Table "trunc_b" references "truncate_a".
43 HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
44 TRUNCATE TABLE truncate_a,trunc_b; -- fail
45 ERROR: cannot truncate a table referenced in a foreign key constraint
46 DETAIL: Table "trunc_e" references "truncate_a".
47 HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
48 TRUNCATE TABLE truncate_a,trunc_b,trunc_e; -- ok
49 TRUNCATE TABLE truncate_a,trunc_e; -- fail
50 ERROR: cannot truncate a table referenced in a foreign key constraint
51 DETAIL: Table "trunc_b" references "truncate_a".
52 HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
53 TRUNCATE TABLE trunc_c; -- fail
54 ERROR: cannot truncate a table referenced in a foreign key constraint
55 DETAIL: Table "trunc_d" references "trunc_c".
56 HINT: Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
57 TRUNCATE TABLE trunc_c,trunc_d; -- fail
58 ERROR: cannot truncate a table referenced in a foreign key constraint
59 DETAIL: Table "trunc_e" references "trunc_c".
60 HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
61 TRUNCATE TABLE trunc_c,trunc_d,trunc_e; -- ok
62 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a; -- fail
63 ERROR: cannot truncate a table referenced in a foreign key constraint
64 DETAIL: Table "trunc_b" references "truncate_a".
65 HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
66 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok
67 TRUNCATE TABLE truncate_a RESTRICT; -- fail
68 ERROR: cannot truncate a table referenced in a foreign key constraint
69 DETAIL: Table "trunc_b" references "truncate_a".
70 HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
71 TRUNCATE TABLE truncate_a CASCADE; -- ok
72 NOTICE: truncate cascades to table "trunc_b"
73 NOTICE: truncate cascades to table "trunc_e"
74 -- circular references
75 ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
76 -- Add some data to verify that truncating actually works ...
77 INSERT INTO trunc_c VALUES (1);
78 INSERT INTO truncate_a VALUES (1);
79 INSERT INTO trunc_b VALUES (1);
80 INSERT INTO trunc_d VALUES (1);
81 INSERT INTO trunc_e VALUES (1,1);
82 TRUNCATE TABLE trunc_c;
83 ERROR: cannot truncate a table referenced in a foreign key constraint
84 DETAIL: Table "truncate_a" references "trunc_c".
85 HINT: Truncate table "truncate_a" at the same time, or use TRUNCATE ... CASCADE.
86 TRUNCATE TABLE trunc_c,truncate_a;
87 ERROR: cannot truncate a table referenced in a foreign key constraint
88 DETAIL: Table "trunc_d" references "trunc_c".
89 HINT: Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
90 TRUNCATE TABLE trunc_c,truncate_a,trunc_d;
91 ERROR: cannot truncate a table referenced in a foreign key constraint
92 DETAIL: Table "trunc_e" references "trunc_c".
93 HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
94 TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e;
95 ERROR: cannot truncate a table referenced in a foreign key constraint
96 DETAIL: Table "trunc_b" references "truncate_a".
97 HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
98 TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e,trunc_b;
99 -- Verify that truncating did actually work
100 SELECT * FROM truncate_a
102 SELECT * FROM trunc_c
104 SELECT * FROM trunc_b
106 SELECT * FROM trunc_d;
111 SELECT * FROM trunc_e;
116 -- Add data again to test TRUNCATE ... CASCADE
117 INSERT INTO trunc_c VALUES (1);
118 INSERT INTO truncate_a VALUES (1);
119 INSERT INTO trunc_b VALUES (1);
120 INSERT INTO trunc_d VALUES (1);
121 INSERT INTO trunc_e VALUES (1,1);
122 TRUNCATE TABLE trunc_c CASCADE; -- ok
123 NOTICE: truncate cascades to table "truncate_a"
124 NOTICE: truncate cascades to table "trunc_d"
125 NOTICE: truncate cascades to table "trunc_e"
126 NOTICE: truncate cascades to table "trunc_b"
127 SELECT * FROM truncate_a
129 SELECT * FROM trunc_c
131 SELECT * FROM trunc_b
133 SELECT * FROM trunc_d;
138 SELECT * FROM trunc_e;
143 DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
144 NOTICE: drop cascades to constraint trunc_e_a_fkey on table trunc_e
145 NOTICE: drop cascades to constraint trunc_b_a_fkey on table trunc_b
146 NOTICE: drop cascades to constraint trunc_e_b_fkey on table trunc_e
147 NOTICE: drop cascades to constraint trunc_d_a_fkey on table trunc_d