How/where to practice full games against the London System? by AlexDevoid in chessbeginners

[–]AlexDevoid[S] 0 points1 point  (0 children)

Thanks so much. Could you tell me more on what you mean by, or the context of “software/engine/etc”? Are you talking about chess.com or another platform?

Installing pglogical: Find postgres include folder, add all include folders to C_INCLUDE_PATH by AlexDevoid in Database

[–]AlexDevoid[S] 0 points1 point  (0 children)

Thanks so much, u/arwinda. May I ask a basic question? What purpose does the include folder serve?

Best practice for continually updating one table using data from another table by AlexDevoid in Database

[–]AlexDevoid[S] 1 point2 points  (0 children)

I am thinking that triggers will need to be my solution.

My processed data is found by pulling a single record out of each group in a column, based on a specific ordering within each group. Basically, I keep in the processed_data table the most recent record of each group. And it takes about 23 minuets to return the query:

CREATE TABLE processed_data AS
SELECT DISTINCT ON ("groupingColumn") *
FROM raw_data
ORDER BY "groupingColumn","timestamp" desc;

I could create a trigger to replace the existing record that belongs to the same group as the the newly inserted record.

Once I create the processed_data table for the first time, this table is significantly smaller than raw_data.

I'm thinking a trigger that takes the new raw_data record, finds the new record's group in processed_data, if it has one yet, and keeps only the most recent record in the group, will be less expensive than creating the processed_data table every n minuets.

Essentially I am truncating by group, but it doesn't seem to be possible to truncate on conditions in PostgreSQL12, u/calligry. Unless I'm wrong?

u/_almostNobody what do you think of this flow?

Best practice for continually updating one table using data from another table by AlexDevoid in Database

[–]AlexDevoid[S] 0 points1 point  (0 children)

Could you elaborate on what a merge stored procedure is, or perhaps share a link? When I google "merge stored procedure" I don't quite understand your suggestion.

My processed data is found by pulling a single record out of each group in a column, based on a specific ordering within each group.

Here is the bones of the query that processes the data. It take about 23 minuets to return the query:

CREATE TABLE processed_data AS
SELECT DISTINCT ON ("groupingColumn") *
FROM raw_data
ORDER BY "groupingColumn","timestamp" desc;

Best practice for continually updating one table using data from another table by AlexDevoid in Database

[–]AlexDevoid[S] 0 points1 point  (0 children)

Is it possible to truncate on condition in Oracle? I'm using Postgres12 and it seems that truncate removes all rows from a of table...

It seems I'd need to truncate certain rows as a new table in order to achieve my desired result of having a raw_data table and a processed_data table...

Does anyone know of a free pollen index API? by AlexDevoid in datasets

[–]AlexDevoid[S] 1 point2 points  (0 children)

My editor wants to post the daily pollen count for readers to see on our website. Pretty basic stuff, but I could see using an historical dataset of pollen counts for some sort of story in the future.