Truncating Tables

Let us understand details related to truncating tables.

  • If you want to delete the data from a table entirely, then TRUNCATE is 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.

  • TRUNCATE is 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 TRUNCATE as 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 CASCADE to truncate the data in child tables as well as in the parent.

%load_ext sql

Copy to clipboard

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

%sql DROP TABLE IF EXISTS user_logins

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%sql DROP TABLE IF EXISTS users

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%sql DROP SEQUENCE IF EXISTS users_user_id_seq

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%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
)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%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)
)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%sql

ALTER TABLE user_logins
    ADD FOREIGN KEY (user_id)
    REFERENCES users(user_id)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

Warning

You will not be able to truncate parent table with out cascade (even when tables are empty)

%sql TRUNCATE TABLE users

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db

Copy to clipboard

---------------------------------------------------------------------------
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 

 in execute(self, line, cell, local_ns)

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/magic.py in (f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

 in execute(self, line, cell, local_ns)

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/magic.py in (f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sql/magic.py in execute(self, line, cell, local_ns)
    215 
    216         try:
--> 217             result = sql.run.run(conn, parsed["sql"], self, user_ns)
    218 
    219             if (

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sql/run.py in run(conn, sql, config, user_namespace)
    365             else:
    366                 txt = sqlalchemy.sql.text(statement)
--> 367                 result = conn.session.execute(txt, user_namespace)
    368             _commit(conn=conn, config=config)
    369             if result and config.feedback:

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params)
   1009             )
   1010         else:
-> 1011             return meth(self, multiparams, params)
   1012 
   1013     def _execute_function(self, func, multiparams, params):

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    296     def _execute_on_connection(self, connection, multiparams, params):
    297         if self.supports_execution:
--> 298             return connection._execute_clauseelement(self, multiparams, params)
    299         else:
    300             raise exc.ObjectNotExecutableError(self)

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1128             distilled_params,
   1129             compiled_sql,
-> 1130             distilled_params,
   1131         )
   1132         if self._has_events or self.engine._has_events:

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1315         except BaseException as e:
   1316             self._handle_dbapi_exception(
-> 1317                 e, statement, parameters, cursor, context
   1318             )
   1319 

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1509             elif should_wrap:
   1510                 util.raise_(
-> 1511                     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   1512                 )
   1513             else:

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    180 
    181         try:
--> 182             raise exception
    183         finally:
    184             # credit to

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1275                 if not evt_handled:
   1276                     self.dialect.do_execute(
-> 1277                         cursor, statement, parameters, context
   1278                     )
   1279 

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    591 
    592     def do_execute(self, cursor, statement, parameters, context=None):
--> 593         cursor.execute(statement, parameters)
    594 
    595     def do_execute_no_params(self, cursor, statement, context=None):

NotSupportedError: (psycopg2.errors.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.

[SQL: TRUNCATE TABLE users]
(Background on this error at: http://sqlalche.me/e/13/tw8g)

Copy to clipboard

%%sql

INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Donald', 'Duck', 'donald@duck.com')

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%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)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%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)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

INSERT INTO user_logins 
    (user_id)
VALUES
    (1),
    (2),
    (3),
    (1),
    (1),
    (4)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.

Copy to clipboard

[]

Copy to clipboard

%sql SELECT * FROM users

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.

Copy to clipboard

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

Note

TRUNCATE with CASCADE will truncate data from child table as well.

%sql TRUNCATE TABLE users CASCADE

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%sql SELECT * FROM users

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.

Copy to clipboard

user_login_id user_id user_login_ts user_ip_addr