CODEWITHSUNDEEP
databasetime-seriesquestdb
Javier Ramirez
Javier Ramirez

Reputation: 4032

Alter column order in QuestDB table

I have a table we created with a specific column order, with the timestamp as the last column, but now I would prefer to change it and move the timestamp to the first place. What would be the best way to do this? I checked the ALTER TABLE docs and couldn't find a solution.

Upvotes: 0

Views: 26

Answers (1)

Javier Ramirez
Javier Ramirez

Reputation: 4032

There is no built-in way of doing this. If you have enough disk space, the easiest way to achieve this is to create a table with the new column order, then run an INSERT INTO SELECT as in

INSERT BATCH 1000000 INTO dummy_table
    SELECT *
    FROM original_table;

And once the data is on the new table just swap names, as in:

RENAME TABLE original_table TO backup_table;
RENAME TABLE dummy_table to original_table;
-- double check you have the right order column and both tables have the same number of rows,partitions...
DROP TABLE backup_table;

You can use a different value for the BATCH parameter, depending on how much memory you want to use during writing the new table.

If your table is too big and you cannot afford to duplicate the whole data, you would always migrate by partition range (using a WHERE on the INSERT INTO...SELECT statement), dropping the partitions from the original table as you are adding new ones.

Upvotes: 0

Related Questions