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. You cannot useDELETE
commands on tables in hot-standby databases; see HOT_STANDBY and READONLY Modes.Warning: RunningEXPLAIN ANALYZE
on anINSERT
,UPDATE
, orDELETE
statement modifies the target table, without warning. To avoid this problem, you can run theEXPLAIN 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 aWHERE
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)