7. DELETE FROM#

7.1. Synopsis#

DELETE FROM { <table_name> | <find_expression> } [, ... ]

[ IN { RANGE <range_spec> | '[' RANGE <range_spec> [, ...] ']' }
    [ WITH DAYS IN ( <day>, <day> ) ]
    [ WITH TIME IN ( <time>, <time> ) ] ]

[ WHERE { <condition> | <duplicate_expression> } ]

range_spec ::=
  ( <timestamp>, <timestamp> )
  ( <timestamp>, <time_offset> )

find_expression ::=
  FIND (
    { TAG = <tag_value> | NOT TAG = <tag_value>
      | PREFIX = 'prefix' | SUFFIX = 'suffix' | TYPE = TS }
    [ AND ... ] [ , RECURSIVE = { TRUE | FALSE } ]
  )

tag_value ::= { 'text' | $table } [ || ... ]

duplicate_expression ::=
  DUPLICATE [ ( <column_name> [, ... ] ) ]

7.2. Description#

DELETE FROM removes rows from one or more tables. Use IN RANGE to limit the operation to one or more time ranges. Use WHERE to delete only rows that match a condition. If you omit both clauses, QuasarDB deletes all rows from the selected tables.

7.3. Column-partitioned tables#

Ordinary filtered, ranged and full deletion work with Column partitioning. IN RANGE selects row $timestamp values, independently of the column used for storage partitioning.

DELETE WHERE DUPLICATE requires its comparison columns to include the partition key, so a duplicate comparison group cannot span physical partitions. The exception is a HASH layout with PARTITIONS 1, which guarantees that all rows share one physical partition. Comparing all columns with bare DUPLICATE includes the key.

For sensor_readings, compare the timestamp, key and value:

DELETE FROM sensor_readings WHERE DUPLICATE($timestamp, sensor_id, value);

A comparison that omits sensor_id is rejected with qdb_e_not_implemented, unless the table uses a one-slot HASH layout. Global duplicate deletion across partitions is unsupported, even if a particular dataset happens to put all matching rows in one partition. See Supported operations and limitations.

7.4. Parameters#

table_name: The name of a table from which to remove rows.
find_expression: Selects multiple tables with a key/value lookup. For more information, refer to key/value lookups.
condition: A Boolean expression that selects the rows to remove. For more information, refer to comparison operators.
duplicate_expression: A condition that selects duplicate rows. Use DUPLICATE(col1, col2, ...) to compare only the specified columns. Include $timestamp when you specify columns. Use DUPLICATE without columns to compare all columns. QuasarDB compares rows separately in each table. Each specified column must exist in all selected tables.
timestamp: An absolute timestamp. Use a date or a date and time. You can use day, second, or nanosecond precision. For more information, refer to timestamps.
time_offset: A relative time offset. Use it with an absolute timestamp. For more information, refer to time offsets.
day: A weekday. Use mon, tue, wed, thu, fri, sat, or sun. Both limits in WITH DAYS IN are inclusive. Thus, WITH DAYS IN (mon, tue) includes Monday and Tuesday.
time: A time of day with minute, second, or nanosecond precision. The first limit in WITH TIME IN is inclusive. The second limit is exclusive. Thus, WITH TIME IN (09:22:00, 09:22:01) excludes a row at exactly 09:22:01.

7.5. Examples#

Delete all rows from table example:

DELETE FROM example

Delete all rows where col is negative:

DELETE FROM example WHERE col < 0

Delete everything from the year 2017 in table example:

DELETE FROM example in range(2017, 2018)

Delete everything before 2017 in table example:

DELETE FROM example in range(1970, 2017)

Delete all the data for the last hour whose column value ‘col’ is equal to 2:

DELETE FROM example in range(now, -1h) WHERE col=2

Delete all the duplicate rows for the last hour:

DELETE FROM example in range(now, -1h) WHERE DUPLICATE

Delete all the rows with duplicated timestamp and ‘col’ values for the last hour:

DELETE FROM example in range(now, -1h) WHERE DUPLICATE('$timestamp', col)

Delete everything from the year 2017 in all tables tagged with ‘nyse’:

DELETE FROM FIND(tag='nyse') in range(2017, 2018)

Delete everything from the last week in all tables tagged with ‘nyse’:

DELETE FROM FIND(tag='nyse') in range(now, -1w)