Heh, I honestly forgot about the
recursive CTE. Certainly worth a try and wouldn't
require installing other extensions.
Thanks for the pointer. Will definitely have to spend some
time wrapping my brain around that one - I’ve done some CTE’s
before, but not recursive that I can recall. Should be fun!
If it helps matters any, my structure is currently the
following:
table “stations” listing station details (name, latitude,
longitude, etc) with a smallint primary key “id"
table “data” with many (many!) data columns (mostly
doubles), a station column that is a smallint referencing the
stations table, and a channel column which is a varchar
containing the *name* of the channel the data came in on.
I will readily accept that this may not be the best
structure for the DB. For example, perhaps the channel column
should be normalized out as has been mentioned a couple of
times as an option. This would make sense, and would certainly
simplify this portion of the project.
If I do go with a lookup table updated by a trigger, what
would be the best option for the query the trigger runs - an
upset (ON CONFLICT DO NOTHING)? Or a query followed by an
insert if needed? The normal case would be that the entry
already exists (millions of hits vs only the occasional insert
needed).
