PostgreSQL – DELETE USING

PostgreSQL has various techniques to delete duplicate rows. One of them is using the DELETE USING statement.

Syntax: DELETE FROM table_name row1 USING table_name row2 WHERE condition;

For the purpose of demonstration let’s set up a sample table(say, basket) that stores fruits as follows:

CREATE TABLE basket(
    id SERIAL PRIMARY KEY,
    fruit VARCHAR(50) NOT NULL
);

Now let’s add some data to the newly created basket table.

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');

Now let’s verify the basket table using the below statement:

SELECT * FROM basket;

This should result into below:

Now that we have set up the sample table, we will query for the duplicates using the following:

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

This should lead to the following results:

Now that we know the duplicate rows we can use the DELETE USING statement to remove duplicate rows as follows:

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

This should remove all duplicate from the table basket, and to verify so use the below query:

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

This should result in the following: