DELETE¶

DELETE removes specific rows from a table.

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

Tip

To delete all rows from a table, see TRUNCATE
To delete columns, see DROP COLUMN

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