Delete table in PostgreSQL fails

Question

I am working with postgreSQL DB, I need to delete a table with this script:

import psycopg2

# Connect to the PostgreSQL database
conn = psycopg2.connect(
    host="your_host",
    database="your_database",
    user="your_username",
    password="your_password"
)

# Create a cursor object to execute SQL commands
cur = conn.cursor()

# Define the name of the table to delete
table_name = "your_table_name"

# Define the SQL query to delete the table
delete_table_query = f"DROP TABLE IF EXISTS {table_name};"

cur.execute(delete_table_query)


conn.commit()
cur.close()
conn.close()

when i execute this code and i check the table deleted i see it deleted and empty but when i check on the size of my DB still the same despite i delete 4gb of data ,what is the problem in this script

The table probably doesn't exist in the database where you run that. Try removing the `IF EXISTS`, then you will see an error. — Laurenz Albe, Feb 21 '23 at 08:23
the table exist already and i store my data on it , i tried also to delete it using the pgadmin , it was deleted and empty but the size of my DB didnt change — Brell, Feb 21 '23 at 08:27
i dont check the disk , i check the" statistics" onglet in pgadmin4 the "size" still the same after deleting data , despite the "last statistics reset" is up to date — Brell, Feb 21 '23 at 10:02
There is a difference between deleting data in a table and dropping a table. If you did as the code shows, `DROP TABLE IF EXISTS {table_name};` there would be no table size to check. Also truncate would have no meaning as there would be no table for it to work on. — Adrian Klaver, Feb 21 '23 at 16:29

Stavros Koureas · Answer 1 · 2023-04-05T15:14:53.413

Table drop in RDBMS in general is not reclaiming the disk space in order to have fast inserts that do not need database request allocation. (When you delete data from the database, the space used by that data is flagged as "free" inside the database file and is available for use by new data rows)

However table drop in RDBMS like dataPostgreSQL should reclaim the size on statistics page of the database. (I tested this into PostgreSQL >= v14 and table drop AND table truncate reclaims the size on statistics page, while the delete from table does not reclaim the size on statistics page).

So do not mix the disk size with database size. In addition there is also difference between drop table, delete from table and truncate table, so do not mix either this cases (as you write in comments that you changed from delete to truncate but in the example you have drop).

For disk size reclaim in PostgreSQL, you can try the following:

to perform truncate before the table drop to reclaim the space
or, execute VACCUM FULL after the table drop to reclaim all possible free space into the database.

Delete table in PostgreSQL fails

1 Answers1