Durga Gadiraju

Truncating Tables in PostgreSQL

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.

In [14]:

%reload_ext sql

In [15]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

In [16]:

%sql DROP TABLE IF EXISTS user_logins

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[16]:

[]

In [17]:

%sql DROP TABLE IF EXISTS users

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[17]:

[]

In [18]:

%sql DROP SEQUENCE IF EXISTS users_user_id_seq

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[18]:

[]

In [19]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[19]:

[]

In [20]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[20]:

[]

In [21]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[21]:

[]

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

In [22]:

%sql TRUNCATE TABLE users

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db

---------------------------------------------------------------------------
FeatureNotSupported                       Traceback (most recent call last)
File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1799, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1798     if not evt_handled:
-> 1799         self.dialect.do_execute(
   1800             cursor, statement, parameters, context
   1801         )
   1803 if self._has_events or self.engine._has_events:

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py:717, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    716 def do_execute(self, cursor, statement, parameters, context=None):
--> 717     cursor.execute(statement, parameters)

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)
Input In [22], in <cell line: 1>()
----> 1 get_ipython().run_line_magic('sql', 'TRUNCATE TABLE users')

File ~/.local/lib/python3.8/site-packages/IPython/core/interactiveshell.py:2285, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   2283     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2284 with self.builtin_trap:
-> 2285     result = fn(*args, **kwargs)
   2286 return result

File ~/.local/lib/python3.8/site-packages/sql/magic.py:217, in SqlMagic.execute(self, line, cell, local_ns)
    214     return
    216 try:
--> 217     result = sql.run.run(conn, parsed["sql"], self, user_ns)
    219     if (
    220         result is not None
    221         and not isinstance(result, str)
   (...)
    224         # Instead of returning values, set variables directly in the
    225         # users namespace. Variable names given by column names
    227         if self.autopandas:

File ~/.local/lib/python3.8/site-packages/sql/run.py:367, 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:

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1286, in Connection.execute(self, statement, *multiparams, **params)
   1282     util.raise_(
   1283         exc.ObjectNotExecutableError(statement), replace_context=err
   1284     )
   1285 else:
-> 1286     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File ~/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:325, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    321 def _execute_on_connection(
    322     self, connection, multiparams, params, execution_options, _force=False
    323 ):
    324     if _force or self.supports_execution:
--> 325         return connection._execute_clauseelement(
    326             self, multiparams, params, execution_options
    327         )
    328     else:
    329         raise exc.ObjectNotExecutableError(self)

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1478, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1466 compiled_cache = execution_options.get(
   1467     "compiled_cache", self.engine._compiled_cache
   1468 )
   1470 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1471     dialect=dialect,
   1472     compiled_cache=compiled_cache,
   (...)
   1476     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1477 )
-> 1478 ret = self._execute_context(
   1479     dialect,
   1480     dialect.execution_ctx_cls._init_compiled,
   1481     compiled_sql,
   1482     distilled_params,
   1483     execution_options,
   1484     compiled_sql,
   1485     distilled_params,
   1486     elem,
   1487     extracted_params,
   1488     cache_hit=cache_hit,
   1489 )
   1490 if has_events:
   1491     self.dispatch.after_execute(
   1492         self,
   1493         elem,
   (...)
   1497         ret,
   1498     )

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1842, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1839             branched.close()
   1841 except BaseException as e:
-> 1842     self._handle_dbapi_exception(
   1843         e, statement, parameters, cursor, context
   1844     )
   1846 return result

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2023, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2021     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2022 elif should_wrap:
-> 2023     util.raise_(
   2024         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2025     )
   2026 else:
   2027     util.raise_(exc_info[1], with_traceback=exc_info[2])

File ~/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py:207, in raise_(***failed resolving arguments***)
    204     exception.__cause__ = replace_context
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle
    212     del exception, replace_context, from_, with_traceback

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1799, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1797                 break
   1798     if not evt_handled:
-> 1799         self.dialect.do_execute(
   1800             cursor, statement, parameters, context
   1801         )
   1803 if self._has_events or self.engine._has_events:
   1804     self.dispatch.after_cursor_execute(
   1805         self,
   1806         cursor,
   (...)
   1810         context.executemany,
   1811     )

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py:717, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    716 def do_execute(self, cursor, statement, parameters, context=None):
--> 717     cursor.execute(statement, parameters)

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: https://sqlalche.me/e/14/tw8g)

In [23]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[23]:

[]

In [24]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[24]:

[]

In [25]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
3 rows affected.

Out[25]:

[]

In [26]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
6 rows affected.

Out[26]:

[]

In [27]:

%sql SELECT * FROM users

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
5 rows affected.

Out[27]:

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	2022-03-13
2	Mickey	Mouse	mickey@mouse.com	None	None	U	True	2022-03-13
3	Gordan	Bradock	gbradock0@barnesandnoble.com	None	h9LAz7p7ub	U	True	2022-03-13
4	Tobe	Lyness	tlyness1@paginegialle.it	None	oEofndp	U	True	2022-03-13
5	Addie	Mesias	amesias2@twitpic.com	None	ih7Y69u56	U	True	2022-03-13

In [28]:

%sql SELECT * FROM user_logins

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
6 rows affected.

Out[28]:

user_login_id	user_id	user_login_ts	user_ip_addr
1	1	2022-03-13 09:16:50.079147	None
2	2	2022-03-13 09:16:50.079147	None
3	3	2022-03-13 09:16:50.079147	None
4	1	2022-03-13 09:16:50.079147	None
5	1	2022-03-13 09:16:50.079147	None
6	4	2022-03-13 09:16:50.079147	None

{note}
`TRUNCATE` with `CASCADE` will truncate data from child table as well.

In [29]:

%sql TRUNCATE TABLE users CASCADE

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[29]:

[]

In [30]:

%sql SELECT * FROM users

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
0 rows affected.

Out[30]:

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	created_dt

In [31]:

%sql SELECT * FROM user_logins

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
0 rows affected.

Out[31]:

user_login_id	user_id	user_login_ts	user_ip_addr

Durga Gadiraju

Truncating Tables in PostgreSQL

Truncating Tables¶

Share this post

Join Our Community

Follow Us

Links

Contact Info

Address

Phone

Email