In CrateDB, after creating a table from data of another table, is it possible to keep the new table updated with the insertion of new lines from the original table?
Query to create the new_table
from enter code here
:
CREATE TABLE "schema"."new_table" AS
SELECT
state,
time,
time - LAG(time, -1, time) OVER (ORDER BY time DESC) AS duration
FROM "schema"."original_table"
ORDER BY timeDESC;
Query I run periodically to keep it the new_table
updated, and which I would like to avoid using:
INSERT INTO "schema"."new_table"
SELECT
process,
time,
time- LAG(time, -1, time) OVER (ORDER BY time DESC) AS duration FROM "mtopcua_car"."original_table" newDataTable
WHERE NOT EXISTS (SELECT time FROM "schema"."new_table" WHERE time = newDataTable.time);
Thanks.