SQL ET POSTGRE 3.12 : Table deletion

Now that we know how to modify tables in our database, the last topic to cover is how to delete a table altogether. We go back to the test database and start by creating a new table that we will then drop. The query begins with CREATE TABLE followed by the name — let's call it training — then a pair of parentheses and a semicolon, just as we are used to.

Creating a sample table

Inside the parentheses we add a few columns: an id of type SERIAL set as our PRIMARY KEY, a product column of type VARCHAR(30) and a price column of type NUMERIC(4,2). We run the query and our small but sufficient training table is created. We confirm by running SELECT * FROM training — the table is indeed there. 
DROP TABLE training;
  • Create a sample table to play with.
  • Drop it with DROP TABLE <name>;.
  • Verify by re-running SELECT * — the relation should no longer exist.
To delete the table, we simply write DROP TABLE followed by the table name. We select the query and run it; it returns successfully. If we then try SELECT * FROM training, PostgreSQL responds with an error: "relation training does not exist". That confirms the table has been removed. To delete a table in PostgreSQL, all you need is a DROP TABLE statement followed by the table name.

Summary

This lesson demonstrates how to delete a table from a PostgreSQL database using the DROP TABLE command. The instructor creates a sample 'trainings' table with ID (serial), product (varchar), and price (numeric) columns, then verifies its creation before removing it and confirming the deletion with a SELECT query that returns an error.

Key points

  • Use DROP TABLE [table_name] to remove a table from a PostgreSQL database
  • Verify successful table deletion by attempting a SELECT query—a 'relation does not exist' error confirms the table was dropped
  • Always ensure you no longer need a table's data before executing DROP TABLE, as deletion is permanent
  • The syntax for deletion is simple and straightforward: no additional parameters or conditions are required

FAQ

How do you delete a table in PostgreSQL?

Execute DROP TABLE followed by the table name (e.g., DROP TABLE trainings). Once the query runs successfully, the table and all its contents are permanently removed from the database.

How can you verify that a table has been deleted?

Attempt to run a SELECT query on the deleted table. If the table no longer exists, PostgreSQL will return an error stating 'relation [table_name] does not exist'.

Is there a way to recover a table after dropping it?

No—DROP TABLE permanently deletes the table and its data. Always verify that you no longer need the table before executing the command, or maintain backups for critical data.