PostgreSQL - DELETE USING

The PostgreSQL DELETE USING statement is a powerful and efficient way to delete duplicate rows from a table, especially when duplicates are based on specific conditions. This method is particularly useful in situations where we need to remove rows by comparing data within the same table or across multiple tables.

In this article, we will explain the DELETE USING syntax, provide examples to demonstrate its functionality and cover best practices for safely deleting duplicate rows in PostgreSQL.

What is DELETE USING in PostgreSQL?

In PostgreSQL, the DELETE USING statement allows us to delete rows based on conditions that involve multiple instances of the same table or multiple tables. It is especially handy for removing duplicates by defining conditions to identify which duplicate rows to keep and which to delete.

Syntax:

DELETE FROM table_name row1 
USING table_name row2 
WHERE condition;

Key Terms

• table_name: The table from which we want to delete rows.
  
• row1 and row2: Aliases for the table, making it possible to refer to the same table in the DELETEand USINGclauses.
  
• condition: The criteria used to identify which rows to delete.

Examples of PostgreSQL DELETE USING

Let’s set up a sample table named basket to demonstrate the DELETE USING statement in PostgreSQL. The basket table stores information about fruits and includes an idcolumn to uniquely identify each row.

Query:

CREATE TABLE basket(
    id SERIAL PRIMARY KEY,
    fruit VARCHAR(50) NOT NULL
);
INSERT INTO basket(fruit) values('apple');
INSERT INTO basket(fruit) values('apple');

INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('orange');

INSERT INTO basket(fruit) values('banana');
SELECT * FROM basket;

Output

Identifying Duplicate Rows

Now that we have set up the sample table, we will query for the duplicates using the following. Before removing duplicates, let us identify them by querying the baskettable. This query groups fruits and counts occurrences to help us identify duplicates.

Query:

SELECT
    fruit,
    COUNT( fruit )
FROM
    basket
GROUP BY
    fruit
HAVING
    COUNT( fruit )> 1
ORDER BY
    fruit;

Output

Example 1: Removing Duplicates Using DELETE USING

Now that we know the duplicate rows we can use the DELETE USINGstatement to remove duplicate rows as follows. The query keeps the row with the smallest id for each fruit and deletes the others.

Query:

DELETE FROM
    basket a
        USING basket b
WHERE
    a.id < b.id
    AND a.fruit = b.fruit;

Explanation:

This query works by comparing each row in the table with every other row. If it finds duplicates ('a.fruit = b.fruit') and the 'id' of the first row ('a.id') is less than the 'id' of the second row ('b.id'), it deletes the row with the smaller 'id'. This should remove all duplicate from the table basket

Verification

To verify that duplicates have been removed, run the following query:

Query:

SELECT
    fruit,
    COUNT( fruit )
FROM
    basket
GROUP BY
    fruit
HAVING
    COUNT( fruit )> 1
ORDER BY
    fruit;

Output

Explanation:

This query should return no rows, indicating all duplicate fruits have been successfully deleted from the baskettable.

Full Table Check: Remaining Rows

After removing duplicates, we can confirm the contents of the baskettable to ensure only unique fruits remain. This Query will indicate that all duplicate rows have been removed, and only one instance of each fruit remains in the table.

Query:

SELECT * FROM basket ORDER BY id;

Output

Important Points About PostgreSQL DELETE USING Statement

• The DELETE USING statement is used to delete rows from a table based on a condition involving multiple tables or multiple instances of the same table.
  
• Aliases can be used for tables to make the query more readable, especially when the same table is referenced multiple times.
  
• Be cautious with the DELETE USING statement as it can delete multiple rows. Ensure that the WHEREcondition is correctly specified to avoid unintentional data loss.
  
• Test the DELETEoperation on a subset of your data to ensure it works as expected.

Conclusion

The DELETE USING statement in PostgreSQL is an effective tool for deleting rows based on complex conditions, such as removing duplicate rows from a table. With options to define specific criteria using aliases and multiple WHERE conditions, DELETE USING is flexible for data maintenance tasks. By following best practices and carefully defining conditions, PostgreSQL users can efficiently clean up data and ensure database integrity.