How to use Post­gr­eSQL DELETE

With Post­gr­eSQL DELETE, you can delete data from a table. The command can be refined using con­di­tions, allowing you to specify the removal of in­di­vidu­al rows. Since deletions are ir­re­vers­ible in Post­gr­eSQL, it’s important to use the command carefully.

What is Post­gr­eSQL’s DELETE?

The DELETE command in Post­gr­eSQL 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 per­man­ently. Because data is deleted per­man­ently, you should exercise caution when using this command.

What is the syntax for Post­gr­eSQL’s DELETE?

The basic syntax of Post­gr­eSQL 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 in­di­vidu­al rows where data should be deleted. To implement multiple con­di­tions, you can use AND or OR.

How to delete data from a table

To get a better idea of how DELETE works in Post­gr­eSQL, 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 Post­gr­eSQL 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 Post­gr­eSQL

Often times, you’ll need to delete an in­di­vidu­al 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 con­di­tions

If you are working with large tables, you may have duplicate entries. To ensure that only one row is deleted, you can use multiple con­di­tions. 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 con­di­tions:

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 con­tain­ing Haines has an id that is less than 3, it remains in the database after the delete command is carried out.