Delete Data

This page has instructions for deleting rows of data from CockroachDB, using the .

Before you begin

Before reading this page, do the following:

or .
.
.
.
that you now want to delete. In the examples on this page, we use sample data imported with the .

Use `DELETE`

To delete rows in a table, use a with a WHERE clause that filters on the columns that identify the rows that you want to delete.

SQL syntax

In SQL, DELETE statements generally take the following form:

DELETE FROM {table} WHERE {filter_column} {comparison_operator} {filter_value}

Where:

is a table with rows that you want to delete.
is the column to filter on.
is a that resolves to TRUE or FALSE (e.g., = ).
is the matching value for the filter.

For detailed reference documentation on the DELETE statement, including additional examples, see the .

Best practices

Here are some best practices to follow when deleting rows:

Limit the number of DELETE statements that you execute. It’s more efficient to delete multiple rows with a single statement than to execute multiple DELETE statements that each delete a single row.
Always specify a WHERE clause in DELETE queries. If no WHERE clause is specified, CockroachDB will delete all of the rows in the specified table.
To delete all of the rows in a table, use a instead of a DELETE statement.
To delete a large number of rows (i.e., tens of thousands of rows or more), use a .
When executing DELETE statements from an application, make sure that you wrap the SQL-executing functions in that can occur under .
Review the performance considerations below.

Examples

Delete rows filtered on a non-unique column

Suppose that you want to delete the vehicle location history data recorded during a specific hour of a specific day. To delete all of the rows in the vehicle_location_histories table where the timestamp is between two values:

DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN '2021-03-17 14:00:00' AND '2021-03-17 15:00:00';

For more information about how to use the built-in SQL client, see the reference docs.

// 'db' is an open database connection

tsOne := "2021-03-17 14:00:00"
tsTwo := "2021-03-17 15:00:00"

if _, err := db.Exec("DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN $1 AND $2", tsOne, tsTwo); err != nil {
  return err
}
return nil

// ds is an org.postgresql.ds.PGSimpleDataSource

String tsOne = "2021-03-17 14:00:00";
String tsTwo = "2021-03-17 15:00:00";

try (Connection connection = ds.getConnection()) {
    PreparedStatement p = connection.prepareStatement("DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN ? AND ?");
    p.setString(1, tsOne);
    p.setString(2, tsTwo);
    p.executeUpdate();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
            e.getMessage());
}

# conn is a psycopg2 connection

tsOne = '2021-03-17 14:00:00'
tsTwo = '2021-03-17 15:00:00'

with conn.cursor() as cur:
    cur.execute(
        "DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN %s AND %s", (tsOne, tsTwo))

If the WHERE clause evaluates to TRUE for a large number of rows (i.e., tens of thousands of rows), use a instead of executing a simple DELETE query.

Delete rows filtered on a unique column

Suppose that you want to delete the promo code data for a specific set of codes. To delete the rows in the promo_codes table where the code matches a string in a set of string values:

DELETE from promo_codes WHERE code IN ('0_explain_theory_something', '100_address_garden_certain', '1000_do_write_words');

For more information about how to use the built-in SQL client, see the reference docs.

// 'db' is an open database connection

codeOne := "0_explain_theory_something"
codeTwo := "100_address_garden_certain"
codeThree := "1000_do_write_words"

if _, err := db.Exec("DELETE from promo_codes WHERE code IN ($1, $2, $3)", codeOne, codeTwo, codeThree); err != nil {
  return err
}
return nil

// ds is an org.postgresql.ds.PGSimpleDataSource

String codeOne = "0_explain_theory_something";
String codeTwo = "100_address_garden_certain";
String codeThree = "1000_do_write_words";

try (Connection connection = ds.getConnection()) {
    PreparedStatement p = connection.prepareStatement("DELETE from promo_codes WHERE code IN(?, ?, ?)");
    p.setString(1, codeOne);
    p.setString(2, codeTwo);
    p.setString(3, codeThree);
    p.executeUpdate();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
            e.getMessage());
}

# conn is a psycopg2 connection

codeOne = '0_explain_theory_something'
codeTwo = '100_address_garden_certain'
codeThree = '1000_do_write_words'

with conn.cursor() as cur:
    cur.execute("DELETE from promo_codes WHERE code IN (%s, %s, %s)", (codeOne, codeTwo, codeThree)),

Performance considerations

Because of the way CockroachDB works under the hood, deleting data from the database does not immediately reduce disk usage. Instead, records are marked as “deleted” and processed asynchronously by a background garbage collection process. Once the marked records are older than , they are eligible to be removed. The garbage collection interval is designed to allow sufficient time for running and . The garbage collection interval is controlled by the setting. The practical implications of the above are:

Deleting data will not immediately decrease disk usage.
If you issue multiple statements in sequence that each delete large amounts of data, each subsequent DELETE statement will run more slowly. For details, see .

For more information about how the storage layer of CockroachDB works, see the .

Get Started

CockroachDB and AI

Feature Overview

Data Resilience

Connect to an Application

Self-Hosted Deployments

Schema Design

Reads and Writes

Stream Data

Cross-Cluster Replication

Multi-Region Capabilities

Optimize Performance

Integrate

Before you begin

Use `DELETE`

SQL syntax

Best practices

Examples

Delete rows filtered on a non-unique column

Delete rows filtered on a unique column

Performance considerations

See also

​Before you begin

​Use DELETE

​SQL syntax

​Best practices

​Examples

​Delete rows filtered on a non-unique column

​Delete rows filtered on a unique column

​Performance considerations

​See also

Before you begin

Use `DELETE`

SQL syntax

Best practices

Examples

Delete rows filtered on a non-unique column

Delete rows filtered on a unique column

Performance considerations

See also