Keep update a table created from another table-CodePudding

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.

CodePudding user response：

MS SQL: You problaby want the select * into new_table from table syntax.

Otherwise: Use the metadata views. To auto generate the fields in your create table dml.

select Name ' ' case when user_type_id = 56 then 'int,' else 'err' end, * from sys.columns where object_id = object_id('T1')

CodePudding user response：

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