How to delete a few rows via SQL in QuestDB?

Question

is there a way to delete a view rows matching a query in QuestDB? I can't find any statement allowing me that.

This would be the best option:

delete from mytable where columnvalue==2;

Thanks!

Disembowel

Answer 1

In QuestDb Update and Delete statement are not supported. At least now. The ways to delete data are:

1. Drop a partition

2. Write a copy of the table without the rows you want to delete, drop table and then rename the table to the one you wanted. Something like

   Create table mytablecopy AS (
   SELECT * FROM mytable where columnvalue != 2
   ) Timstamp(...) PARTITION BY ...;
   
   DROP TABLE mytable;
   RENAME table mytablecopy TO mytable;
   

These are costly workarounds for exceptional cases.

Mcbryde

Just a note about dropping partitions, there is some documentation for data retention at the following page which describes strategies for dropping stale data by date: questdb.io/docs/operations/data-retention – Howdah

Answer 2

Updates are allowed in questdb now. In my opinion a much better option is to have an extra column in all your tables called something like isDeleted and use the the update query to maintain what is deleted and whats not. Another note here would be to add indexing on this column for efficiency.

see this for more details : https://questdb.io/docs/develop/update-data#postgres-compatibility

See the example below how to use the update query :

"use strict"

const { Client } = require("pg")

const start = async () => {
  const client = new Client({
    database: "qdb",
    host: "127.0.0.1",
    password: "quest",
    port: 8812,
    user: "admin",
    options: "-c statement_timeout=300000"
  })
  await client.connect()

  const createTable = await client.query(
    "CREATE TABLE IF NOT EXISTS trades (ts TIMESTAMP, date DATE, name STRING, value INT) timestamp(ts);"
  )
  console.log(createTable)

  for (let rows = 0; rows < 10; rows++) {
    // Providing a 'name' field allows for prepared statements / bind variables
    let now = new Date().toISOString()
    const query = {
      name: "insert-values",
      text: "INSERT INTO trades VALUES($1, $2, $3, $4);",
      values: [now, now, "node pg prep statement", rows],
    }
    await client.query(query)
  }

  const updateData = await client.query(
          "UPDATE trades SET name = 'update example', value = 123 WHERE value > 7;"
  )
  console.log(updateData)

  await client.query("COMMIT")

  const readAll = await client.query("SELECT * FROM trades")
  console.log(readAll.rows)

  await client.end()
}

start()
  .then(() => console.log("Done"))
  .catch(console.error)

Parole