Truncating Tables
Let us understand details related to truncating tables.
-
If you want to delete the data from a table entirely, then
TRUNCATEis the fastest way to do so. -
Irrespective of size of the table, data can be cleaned up with in no time.
-
Truncate operations can be rolled back.
-
TRUNCATEis a DDL statement. In Postgres, DDL statements are not auto committed. In most of the databases, DDL statements are committed automatically. -
One cannot truncate the table with only DML permissions.
-
As part of the web or mobile applications, we typically will not have
TRUNCATEas part of the core logic. -
In Data Engineering or ETL applications, it is used more commonly to truncate intermediate or stage tables.
-
If we have to truncate multiple related tables at the same time, then typically we truncate child tables first and then parent tables.
-
We can also use
CASCADEto truncate the data in child tables as well as in the parent.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
%sql DROP TABLE IF EXISTS user_logins
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql DROP TABLE IF EXISTS users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql DROP SEQUENCE IF EXISTS users_user_id_seq
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
user_first_name VARCHAR(30) NOT NULL,
user_last_name VARCHAR(30) NOT NULL,
user_email_id VARCHAR(50) NOT NULL,
user_email_validated BOOLEAN,
user_password VARCHAR(200),
user_role VARCHAR(1),
is_active BOOLEAN,
created_dt DATE DEFAULT CURRENT_DATE
)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE TABLE user_logins (
user_login_id SERIAL PRIMARY KEY,
user_id INT,
user_login_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
user_ip_addr VARCHAR(20)
)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
ALTER TABLE user_logins
ADD FOREIGN KEY (user_id)
REFERENCES users(user_id)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql TRUNCATE TABLE users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
---------------------------------------------------------------------------
FeatureNotSupported Traceback (most recent call last)
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
1276 self.dialect.do_execute(
-> 1277 cursor, statement, parameters, context
1278 )
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
592 def do_execute(self, cursor, statement, parameters, context=None):
--> 593 cursor.execute(statement, parameters)
594
FeatureNotSupported: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "user_logins" references "users".
HINT: Truncate table "user_logins" at the same time, or use TRUNCATE ... CASCADE.
The above exception was the direct cause of the following exception:
NotSupportedError Traceback (most recent call last)
<ipython-input-154-a8605a816166> in <module>
----> 1 get_ipython().run_line_magic('sql', 'TRUNCATE TABLE users')
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/interactiveshell.py in run_line_magic(self, magic_name, line, _stack_depth)
2324 kwargs['local_ns'] = sys._getframe(stack_depth).f_locals
2325 with self.builtin_trap:
-> 2326 result = fn(*args, **kwargs)
2327 return result
2328
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Donald', 'Duck', 'donald@duck.com')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, is_active)
VALUES ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', true)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%%sql
INSERT INTO users
(user_first_name, user_last_name, user_email_id, user_password, user_role, is_active)
VALUES
('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true),
('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true),
('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.
[]
%%sql
INSERT INTO user_logins
(user_id)
VALUES
(1),
(2),
(3),
(1),
(1),
(4)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.
[]
%sql SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|---|---|---|---|---|---|---|---|
| 1 | Donald | Duck | donald@duck.com | None | None | None | None | 2020-11-23 |
| 2 | Mickey | Mouse | mickey@mouse.com | None | None | U | True | 2020-11-23 |
| 3 | Gordan | Bradock | gbradock0@barnesandnoble.com | None | h9LAz7p7ub | U | True | 2020-11-23 |
| 4 | Tobe | Lyness | tlyness1@paginegialle.it | None | oEofndp | U | True | 2020-11-23 |
| 5 | Addie | Mesias | amesias2@twitpic.com | None | ih7Y69u56 | U | True | 2020-11-23 |
%sql SELECT * FROM user_logins
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.
| user_login_id | user_id | user_login_ts | user_ip_addr |
|---|---|---|---|
| 1 | 1 | 2020-11-23 16:44:08.289602 | None |
| 2 | 2 | 2020-11-23 16:44:08.289602 | None |
| 3 | 3 | 2020-11-23 16:44:08.289602 | None |
| 4 | 1 | 2020-11-23 16:44:08.289602 | None |
| 5 | 1 | 2020-11-23 16:44:08.289602 | None |
| 6 | 4 | 2020-11-23 16:44:08.289602 | None |
%sql TRUNCATE TABLE users CASCADE
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|
%sql SELECT * FROM user_logins
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.
| user_login_id | user_id | user_login_ts | user_ip_addr |
|---|