DELETE ​

Delete rows from a table based on the results of a condition.

Note: The Yellowbrick column store is designed to optimally store, compress, and organize data for high-volume read-oriented analytics. As a result, when data is deleted it continues to exist in the row store for a period of time until it can be efficiently removed ("garbage-collected" or "vacuumed") and data can be reorganized. These optimizations are fully automatic and require no administration or manual interaction.

[ WITH name AS (subquery) [, ...] ]
DELETE FROM table_name [ * ] [ [ AS ] alias ]
   [ USING list ]
   [ WHERE condition ]

WITH

See WITH Clause for syntax details. You can reference a WITH subquery in the USING clause of the DELETE statement.

table_name

Specify the name of a table in the current database.

Note: You cannot use DELETE commands on tables in remote databases; see Cross-Database Queries.

Warning: Running EXPLAIN ANALYZE on an INSERT, UPDATE, or DELETE statement modifies the target table, without warning. To avoid this problem, you can run the EXPLAIN ANALYZE statement inside a transaction, then roll it back if needed.

USING

Name one or more tables or table references, which in turn can be referenced in WHERE clause conditions for the DELETE operation. Do not list the target table for the DELETE (except for self-join purposes).

WHERE

Use this WHERE clause just as you would use the WHERE clause in a SELECT statement. Define a condition on either the target table or a table that is listed in the USING clause. For example, you can join the target table to another listed table to qualify rows for the DELETE operation.

DELETE FROM table without a WHERE clause deletes all of the rows from the table.

For example, delete rows from the match table that fall within a date range for the matchday column:

premdb=# delete from match 
where extract(year from matchday) between 1992 and 1999;
DELETE 3101

For example, delete rows from the match table based on a join to the season table:

premdb=# delete from match 
using season 
where match.seasonid=season.seasonid;
DELETE 8606

This example uses a WITH clause that contains a ROW_NUMBER function. This statement removes duplicates from the season table based on values in the seasonid column. The system column ROWUNIQUE is used to find matching rows.

premdb=# insert into season(seasonid) values(10);
INSERT 0 1
premdb=# select * from season where seasonid=10;
 seasonid | season_name | numteams | winners 
----------+-------------+----------+---------
      10 | 2001-2002   |       20 | Arsenal
      10 | [NULL]      |   [NULL] | [NULL]
(2 rows)

premdb=# with rows(rid, rn) as (select rowunique, row_number() over(partition by seasonid order by seasonid) from season) 
delete from season using rows where rows.rn>1 and season.rowunique=rows.rid;
DELETE 1
premdb=# select * from season where seasonid=10;
seasonid | season_name | numteams | winners 
----------+-------------+----------+---------
      10 | 2001-2002   |       20 | Arsenal
(1 row)