Title
#users-public
e

ExistentialKev

08/31/2022, 1:43 PM
There was some bad data inserted into the db that I want to remove from the table but this sql command does not seem to be working... how do I remove data from a table?
ALTER TABLE 'mlb' DROP PARTITION
WHERE timestamp 
            "BETWEEN to_timestamp('2022-08-29:07:10:11.000000Z', 'yyyy-MM-dd:HH:mm:ss.SSSUUUZ') 
            "AND to_timestamp('2022-08-29:30:00:00.000000Z', 'yyyy-MM-dd:HH:mm:ss.SSSUUUZ')"
Pei

Pei

08/31/2022, 1:57 PM
e

ExistentialKev

08/31/2022, 1:58 PM
Yeah that's the example I gave in my comment..
1:58 PM
but it doesn't work... the data always remains
g

gmaurice

08/31/2022, 1:58 PM
I think the problem is the double quotes you put before
BETWEEN
and around
AND
e

ExistentialKev

08/31/2022, 2:37 PM
Sorry I copied this from our java backend... so I had some quotes in there but I ran this
ALTER TABLE 'mlb' DROP PARTITION
WHERE timestamp BETWEEN to_timestamp('2022-08-29:07:00:00.000000Z', 'yyyy-MM-dd:HH:mm:ss.SSSUUUZ')
AND to_timestamp('2022-08-30:07:20:00.000000Z', 'yyyy-MM-dd:HH:mm:ss.SSSUUUZ')
2:38 PM
But when I try to search for the same time stamp, the bad entries are still there
Pei

Pei

08/31/2022, 3:12 PM
I’ll get someone to check it. How is your table partitioned?
e

ExistentialKev

08/31/2022, 3:13 PM
Whatever is default we haven't partitioned the table in particular, is that why this wont work?
Pei

Pei

08/31/2022, 3:18 PM
afaik - you have to have partitioned table to be able to drop partitions, here is some basic info: https://questdb.io/docs/concept/partitions so you want to remove specific rows or a block of (timespan) of them?
j

javier ramirez

08/31/2022, 3:19 PM
Depending on how the table was created, there might be default partitioning. If you explicitly created the table issuing a
CREATE table
statement, then there is no default strategy. But if table was created automatically when using ILP, then the default partitioning applies
3:19 PM
You can go to the web console, right click on the table name and click on the
"copy schema to clipboard"
(or something like that) option to see if the table is indeed partitioned
3:20 PM
This is what I get in one of my demo tables where I didn’t specify any partitioning
CREATE TABLE 'ilp_testXX' (
device_type _SYMBOL_ capacity 256 CACHE,
duration_ms _LONG_,
lat _DOUBLE_,
lon _DOUBLE_,
measure1 _LONG_,
measure2 _LONG_,
speed _LONG_,
timestamp
TIMESTAMP
) _timestamp_ (_timestamp_) PARTITION BY DAY;
e

ExistentialKev

08/31/2022, 3:20 PM
Yeah I don't believe we did anything particular with partitioning and the tables were created with the simple
CREATE table
function. @Pei yeah had two inserts that were bad and I wanted to drop them based on the time they were entered
j

javier ramirez

08/31/2022, 3:21 PM
a workaround now would be creating a new table with the desired partitioning strategy, then doing a select into the new table, dropping the old one and renaming the new to the old name
Pei

Pei

08/31/2022, 3:29 PM
I think it’s also the same workaround if you intend to only get rid of specific rows instead of the whole partition, like creating a new table from the existing one with the rows filtered out. (javier correct me if i’m wrong here.)
j

javier ramirez

08/31/2022, 3:29 PM
yep. I was just adding the partitioning bit so in the future it would be easier to drop/replace a partition
e

ExistentialKev

08/31/2022, 3:42 PM
I see... the data set is quite large so I was hoping there would be a simpler way
j

javier ramirez

08/31/2022, 3:43 PM
DELETE is in the roadmap, but not implemented yet. Until then, you can delete just a whole partition or a whole table
Pei

Pei

08/31/2022, 3:44 PM
as Javier said, feel free to leave some comments to the github issue on our roadmap: 🙂 https://github.com/questdb/roadmap/issues/10
j

javier ramirez

08/31/2022, 3:45 PM
a second workaround is adding an is_deleted column, and do logical deletion. But then you need to add the
where
condition to all of your queries, so not sure you want to do that. Just to give you all the options