0

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.

drypatrick
  • 437
  • 1
  • 4
  • 17
  • 1
    May I ask why a view would not be a possible solution for the above? I.e. `CREATE VIEW xx AS stmt`? – proddata Jan 28 '23 at 10:12
  • I'm pretty new to CrateDB, maybe is the proper solution, checking out the documentation about ’VIEW’, thanks for the hint! – drypatrick Jan 28 '23 at 10:24
  • 1
    Just FYI, while we typically monitor stackoverflow for questions tagged with crate/cratedb, there also is http://community.crate.io – proddata Jan 28 '23 at 12:06
  • Thanks for the suggestion! If you can place an answer in place of the comment, I will mark it as solved! – drypatrick Jan 28 '23 at 12:23

1 Answers1

1

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

proddata
  • 216
  • 1
  • 7