DELETE

DELETE removes specific rows from a table.

See more information about how SQream DB deletes data in the Deleting data guide.

Tip

Permissions

The role must have the DELETE and SELECT permissions at the table level.

Syntax

delete_table_statement ::=
    DELETE FROM [schema_name.]table_name [ WHERE value_expr ]
    ;

chunk_cleanup_statement ::=
    SELECT CLEANUP_CHUNKS ( 'schema_name', 'table_name' )
    ;

extent_cleanup_statement ::=
    SELECT CLEANUP_EXTENTS ( 'schema_name', 'table_name' )
    ;

table_name ::= identifier

schema_name ::= identifier

Parameters

Parameter Description
schema_name The name of the schema for the table.
table_name The name of the table to delete rows from.
value_expr An expression that returns Boolean values using columns, such as <column> = <value>. Rows that match the expression will be deleted.

How SQream DB deletes data

Deleting data in SQream DB is a two-step process. First, SQream DB marks rows as deleted, but they remain on-disk until a cleanup process is initiated.

See more information about how SQream DB deletes data in the Deleting data guide.

Notes

  • ALTER TABLE and other DDL operations are blocked on tables that require clean-up.
  • The value expression for deletion can’t be the result of a subquery or a join.
  • SQream DB may prevent a very long delete process. If the estimated time is beyond the threshold, the error message will explain how to override this limitation and continue the process.

Examples

Deleting values from a table

farm=> SELECT * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100
6,\N,\N

6 rows

farm=> DELETE FROM cool_animals WHERE weight > 1000;
executed

farm=> SELECT * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
6,\N,\N

4 rows

Deleting values based on more complex predicates

farm=> SELECT * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100
6,\N,\N

6 rows

farm=> DELETE FROM cool_animals WHERE weight > 1000;
executed

farm=> SELECT * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
6,\N,\N

4 rows

Identifying and cleaning up tables

List tables that haven’t been cleaned up

farm=> SELECT t.table_name FROM sqream_catalog.delete_predicates dp
   JOIN sqream_catalog.tables t
   ON dp.table_id = t.table_id
   GROUP BY 1;
cool_animals

1 row

Identify predicates for clean-up

farm=> SELECT delete_predicate FROM sqream_catalog.delete_predicates dp
   JOIN sqream_catalog.tables t
   ON dp.table_id = t.table_id
   WHERE t.table_name = 'cool_animals';
weight > 1000

1 row

Triggering a cleanup

-- Chunk reorganization (SWEEP)
farm=> SELECT CLEANUP_CHUNKS('public','cool_animals');
executed

-- Delete leftover files (VACUUM)
farm=> SELECT CLEANUP_EXTENTS('public','cool_animals');
executed


farm=> SELECT delete_predicate FROM sqream_catalog.delete_predicates dp
   JOIN sqream_catalog.tables t
   ON dp.table_id = t.table_id
   WHERE t.table_name = 'cool_animals';

0 rows