With Post­greSQL 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­vid­ual rows. Since deletions are ir­re­versible in Post­greSQL, it’s important to use the command carefully.

What is Post­greSQL’s DELETE?

The DELETE command in Post­greSQL 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­ma­nent­ly. Because data is deleted per­ma­nent­ly, you should exercise caution when using this command.

Dedicated Servers
Per­for­mance through in­no­va­tion
  • Dedicated en­ter­prise hardware
  • Con­fig­urable hardware equipment
  • ISO-certified data centers

What is the syntax for Post­greSQL’s DELETE?

The basic syntax of Post­greSQL DELETE is as follows:

DELETE FROM table_name
WHERE [condition];
post­gresql

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

Note

Before deleting data, ensure you have an up-to-date backup of the database. You can also execute the delete operation within a trans­ac­tion. This helps prevent ac­ci­den­tal data loss if the command is executed in­cor­rect­ly.

How to delete data from a table

To get a better idea of how DELETE works in Post­greSQL, 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|New York|
|2|Sullivan|Los Angeles|
|3|Myers|Chicago|
|4|Haines|Houston|
post­gresql

If you use Post­greSQL 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;
post­gresql

How to delete a row in Post­greSQL

Often times, you’ll need to delete an in­di­vid­ual 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;
post­gresql

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;
post­gresql

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.

Go to Main Menu