How to delete a few rows via SQL?

TL;DR

In QuestDb Update and Delete statement are not supported. The ways to delete data are: - Drop a partition - 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.

Steps

1. In QuestDb Update and Delete statement are not supported. At least now. The ways to delete data are: - Drop a partition - 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.

When to use

You are seeing this: I can't find any statement allowing me that. Use this skill when you run into "How to delete a few rows via SQL?".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Source: Stack Overflow #65976247 (accepted answer, 10 votes): 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!

Verified resolution: In QuestDb Update and Delete statement are not supported. At least now. The ways to delete data are: - Drop a partition - 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.

Source URL: https://stackoverflow.com/questions/65976247/how-to-delete-a-few-rows-via-sql