[SOLVED] Keep update a table created from another table

Keep update a table created from another table

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.

Solution

Depending on how expensive the query is, a view might just do the job:

CREATE VIEW "schema"."new_view" AS
SELECT
    state,
    time,
    time - LAG(time, -1, time) OVER (ORDER BY time DESC) AS duration
FROM "schema"."original_table"
ORDER BY time DESC;

CrateDB documentation: https://crate.io/docs/crate/reference/en/5.1/general/ddl/views.html