How to use PostgreSQL DELETE

With PostgreSQL DELETE, you can delete data from a table. The command can be refined using conditions, allowing you to specify the removal of individual rows. Since deletions are irreversible in PostgreSQL, it’s important to use the command carefully.

What is PostgreSQL’s DELETE?

The DELETE command in PostgreSQL is used to delete entries from a table. Using the WHERE clause, you can select certain rows to delete. Without the WHERE clause, all of the data in the table you specify will be deleted permanently. Because data is deleted permanently, you should exercise caution when using this command.

What is the syntax for PostgreSQL’s DELETE?

The basic syntax of PostgreSQL DELETE is as follows:

DELETE FROM table_name
WHERE [condition];

The DELETE FROM command initiates the deletion of rows in the table that you specify. The WHERE clause allows you to specify individual rows where data should be deleted. To implement multiple conditions, you can use AND or OR.

How to delete data from a table

To get a better idea of how DELETE works in PostgreSQL, let’s take a look at a practical example. First, we’re going to use the CREATE TABLE command to create a table named ‘customer_list’. Then, we’re going to fill it in using INSERT INTO. The table has three columns (id, name and city) and contains four entries:

|id|name|city|
|-|-|-|
|1|Haines|Sheffield|
|2|Sullivan|Glasgow|
|3|Smith|Newcastle|
|4|Haines|Belfast|

If you use PostgreSQL DELETE without a condition, all of the data in the table will be deleted. The table structure itself, however, won’t be deleted. Here’s what the command looks like:

DELETE FROM customer_list;

How to delete a row in PostgreSQL

Often times, you’ll need to delete an individual row. You can do this by including a WHERE clause. For example, let’s say we want to delete Sullivan (id number 2) from our list. We can use the following code to do so:

DELETE FROM customer_list
WHERE id = 2;

How to specify a row using multiple conditions

If you are working with large tables, you may have duplicate entries. To ensure that only one row is deleted, you can use multiple conditions. In the table above, we have two customers named Haines, but we only want to delete the second entry. To do this, we can combine two conditions:

DELETE FROM customer_list
WHERE name = 'Haines'
AND id >= 3;

This command deletes all rows with the name Haines that have an id that is greater than or equal to 3. Since the first entry containing Haines has an id that is less than 3, it remains in the database after the delete command is carried out.