Durga Gadiraju

Dropping Tables in PostgreSQL

Dropping Tables¶

Let us go through the details related to dropping tables.

We can drop table using DROP TABLE.
All the direct dependent objects such as indexes, primary key constraints, unique constraints, not null constraints will automatically be dropped.
Sequences will be dropped only if the sequence is owned by the column.
If there are child tables for the table being dropped, then we need to specify CASCADE.
Using CASCADE will drop the constraints from the child table, but not the child tables themselves.
We can also drop the foreign key constraints before dropping the parent table instead of using CASCADE.

In [1]:

%load_ext sql

In [2]:

%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 [3]:

%sql DROP TABLE IF EXISTS user_logins

Done.

Out[3]:

[]

In [4]:

%sql DROP TABLE IF EXISTS users

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

Out[4]:

[]

In [5]:

%sql DROP SEQUENCE IF EXISTS users_user_id_seq

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

Out[5]:

[]

In [6]:

%%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[6]:

[]

In [7]:

%%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[7]:

[]

In [8]:

%%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[8]:

[]

In [9]:

%%sql

SELECT * FROM information_schema.tables
WHERE table_name IN ('users', 'user_logins')

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

Out[9]:

table_catalog	table_schema	table_name	table_type	self_referencing_column_name	reference_generation	user_defined_type_catalog	user_defined_type_schema	user_defined_type_name	is_insertable_into	is_typed	commit_action
itversity_retail_db	public	users	BASE TABLE	None	None	None	None	None	YES	NO	None
itversity_retail_db	public	user_logins	BASE TABLE	None	None	None	None	None	YES	NO	None

In [10]:

%%sql

SELECT * FROM information_schema.sequences
WHERE sequence_name = 'users_user_id_seq'

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

Out[10]:

sequence_catalog	sequence_schema	sequence_name	data_type	numeric_precision	numeric_precision_radix	numeric_scale	start_value	minimum_value	maximum_value	increment	cycle_option
itversity_retail_db	public	users_user_id_seq	integer	32	2	0	1	1	2147483647	1	NO

In [11]:

%%sql

SELECT * FROM information_schema.sequences
WHERE sequence_name = 'user_logins_user_login_id_seq'

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

Out[11]:

sequence_catalog	sequence_schema	sequence_name	data_type	numeric_precision	numeric_precision_radix	numeric_scale	start_value	minimum_value	maximum_value	increment	cycle_option
itversity_retail_db	public	user_logins_user_login_id_seq	integer	32	2	0	1	1	2147483647	1	NO

{error}
We will not be able to drop the parent tables with out dropping the child tables or specifying `CASCADE`. Using `CASCADE` will not drop child tables, it only drops the foreign key constraints.

In [12]:

%sql DROP TABLE users

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

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

DependentObjectsStillExist: cannot drop table users because other objects depend on it
DETAIL:  constraint user_logins_user_id_fkey on table user_logins depends on table users
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


The above exception was the direct cause of the following exception:

InternalError                             Traceback (most recent call last)
Input In [12], in <cell line: 1>()
----> 1 get_ipython().run_line_magic('sql', 'DROP 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)

InternalError: (psycopg2.errors.DependentObjectsStillExist) cannot drop table users because other objects depend on it
DETAIL:  constraint user_logins_user_id_fkey on table user_logins depends on table users
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

[SQL: DROP TABLE users]
(Background on this error at: https://sqlalche.me/e/14/2j85)

In [13]:

%%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[13]:

[]

In [14]:

%%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[14]:

[]

In [15]:

%%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[15]:

[]

In [16]:

%%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[16]:

[]

In [17]:

%sql DROP TABLE users CASCADE

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

Out[17]:

[]

In [18]:

%%sql

SELECT * FROM information_schema.tables
WHERE table_name IN ('users', 'user_logins')

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

Out[18]:

table_catalog	table_schema	table_name	table_type	self_referencing_column_name	reference_generation	user_defined_type_catalog	user_defined_type_schema	user_defined_type_name	is_insertable_into	is_typed	commit_action
itversity_retail_db	public	user_logins	BASE TABLE	None	None	None	None	None	YES	NO	None

In [19]:

%%sql

SELECT * FROM information_schema.sequences
WHERE sequence_name = 'users_user_id_seq'

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

Out[19]:

sequence_catalog	sequence_schema	sequence_name	data_type	numeric_precision	numeric_precision_radix	numeric_scale	start_value	minimum_value	maximum_value	increment	cycle_option

In [20]:

%sql SELECT * FROM user_logins

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

Out[20]:

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

Durga Gadiraju

Dropping Tables in PostgreSQL

Dropping Tables¶

Share this post

Join Our Community

Follow Us

Links

Contact Info

Address

Phone

Email