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" via foreign key constraint "trunc_b_a_fkey".
43 HINT: Truncate table "trunc_b" at the same time.
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" via foreign key constraint "trunc_e_a_fkey".
47 HINT: Truncate table "trunc_e" at the same time.
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" via foreign key constraint "trunc_b_a_fkey".
52 HINT: Truncate table "trunc_b" at the same time.
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" via foreign key constraint "trunc_d_a_fkey".
56 HINT: Truncate table "trunc_d" at the same time.
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" via foreign key constraint "trunc_e_b_fkey".
60 HINT: Truncate table "trunc_e" at the same time.
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" via foreign key constraint "trunc_b_a_fkey".
65 HINT: Truncate table "trunc_b" at the same time.
66 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok
67 -- circular references
68 ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
69 -- Add some data to verify that truncating actually works ...
70 INSERT INTO trunc_c VALUES (1);
71 INSERT INTO truncate_a VALUES (1);
72 INSERT INTO trunc_b VALUES (1);
73 INSERT INTO trunc_d VALUES (1);
74 INSERT INTO trunc_e VALUES (1,1);
75 TRUNCATE TABLE trunc_c;
76 ERROR: cannot truncate a table referenced in a foreign key constraint
77 DETAIL: Table "trunc_d" references "trunc_c" via foreign key constraint "trunc_d_a_fkey".
78 HINT: Truncate table "trunc_d" at the same time.
79 TRUNCATE TABLE trunc_c,trunc_d;
80 ERROR: cannot truncate a table referenced in a foreign key constraint
81 DETAIL: Table "trunc_e" references "trunc_c" via foreign key constraint "trunc_e_b_fkey".
82 HINT: Truncate table "trunc_e" at the same time.
83 TRUNCATE TABLE trunc_c,trunc_d,trunc_e;
84 ERROR: cannot truncate a table referenced in a foreign key constraint
85 DETAIL: Table "truncate_a" references "trunc_c" via foreign key constraint "truncate_a_col1_fkey".
86 HINT: Truncate table "truncate_a" at the same time.
87 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;
88 ERROR: cannot truncate a table referenced in a foreign key constraint
89 DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
90 HINT: Truncate table "trunc_b" at the same time.
91 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;
92 -- Verify that truncating did actually work
93 SELECT * FROM truncate_a
99 SELECT * FROM trunc_d;
104 SELECT * FROM trunc_e;
109 DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
110 NOTICE: drop cascades to constraint trunc_e_a_fkey on table trunc_e
111 NOTICE: drop cascades to constraint trunc_b_a_fkey on table trunc_b
112 NOTICE: drop cascades to constraint trunc_e_b_fkey on table trunc_e
113 NOTICE: drop cascades to constraint trunc_d_a_fkey on table trunc_d