Title
#users-public
t

Tihomir Dimov

09/27/2022, 4:21 PM
Hi I have the following table:
CREATE TABLE 'test_table' (
  last TIMESTAMP,
  timestamp TIMESTAMP
) timestamp (last) PARTITION BY DAY;
And I have accidentally saved the following timestamp in
last
field :
54709-05-18T18:12:44.600000Z
How can I remove the wrong records? If I try
ALTER TABLE test_table DROP PARTITION LIST '54709-05-18';
I'm getting the following error:
'YYYY-MM-DD' expected[errno=0]
Alex Pelagenko

Alex Pelagenko

09/27/2022, 5:26 PM
try using WHERE with Drop partition
Bolek Ziobrowski

Bolek Ziobrowski

09/28/2022, 7:21 AM
https://github.com/questdb/questdb/issues/2529 is likely related to what you're trying to do .
Newskooler

Newskooler

09/28/2022, 11:29 AM
shouldn’t QuestDB just not allow to save such “nonsense” timestamp?
Bolek Ziobrowski

Bolek Ziobrowski

09/29/2022, 8:38 AM
It's within ts range so it's allowed . Largest ts is 294247-01-10T04:00:54.775807Z .
t

Tihomir Dimov

09/29/2022, 1:23 PM
@Bolek Ziobrowski shouldn’t
DROP PARTITION LIST '54709-05-18';
allows ts range too?
Bolek Ziobrowski

Bolek Ziobrowski

09/29/2022, 1:26 PM
I don't think it's possible to drop latest partition at the moment . See issue I mentioned.
t

Tihomir Dimov

09/29/2022, 1:27 PM
The error is not about the latest partition but about the format
'YYYY-MM-DD' expected[errno=0]
1:28 PM
@Alex Pelagenko Unfortunately droping partitions using boolean expression did not help.. Query:
ALTER TABLE test_table
DROP PARTITION
WHERE last = to_timestamp('54709-05-17', 'yyyyy-MM-dd'); // I have tried with 'yyyy-MM-dd' as format too.
I am getting a success message, but when I do
test_table LIMIT -1
the wrong record with
'54709-05-18'
in the designated timestamp column "last" is still there.
Newskooler

Newskooler

09/29/2022, 1:32 PM
It’s because that’s the last partition
1:32 PM
Last partitions cannot be dropped 😕 i raised an issue about it
Alex Pelagenko

Alex Pelagenko

09/29/2022, 1:45 PM
we’re working to make it possible next release
1:46 PM
for now I guess your only way is to copy the table with
create table b as  select * from a where last < now() timestamp(last) partition by day
.
Bolek Ziobrowski

Bolek Ziobrowski

09/29/2022, 1:47 PM
@Tihomir Dimov Can't you use the other syntax ? something like : ALTER TABLE test_table DROP PARTITION WHERE timestamp > to_timestamp('9999-05-18', 'yyyyy-MM-dd');
Newskooler

Newskooler

09/29/2022, 1:48 PM
t

Tihomir Dimov

09/29/2022, 1:55 PM
@Newskooler It is not only about the last partition. The thing is that the query gives back success msg but does nothing to the table
Newskooler

Newskooler

09/29/2022, 1:55 PM
Yeah - that happens when you try to drop the last partition. It does not fail.
t

Tihomir Dimov

09/29/2022, 1:57 PM
No, if I try to DROP last partition from another table I am getting an error msg "could not remove partition '2022-09-29'"
Newskooler

Newskooler

09/29/2022, 1:58 PM
yes… you’re correct. I remember now. Then I don’t know 😕 I guess open a new issue about that.
t

Tihomir Dimov

09/29/2022, 2:00 PM
@Bolek Ziobrowski The query gives success message back, but no changes are applied to the table. The '54709-05-18' is the last partition of the table of course but I am expecting an error if nothing is changed in the table. So I think the last option is the suggestion from Alex