My database has two tables that are connected,
CREATE TABLE IF NOT EXISTS cgi_track_stateTbl ( id int PRIMARY KEY, val TEXT ); TRUNCATE TABLE cgi_track_stateTbl; INSERT INTO cgi_track_stateTbl VALUES (0, 'CGI_TRK_STANDBY'), (1, 'CGI_TRK_SINGLE_DELTA_HV'), (2, 'CGI_TRK_HOLD_ATTITUDE'), (3, 'CGI_TRK_LOWFS_DELTA_HV') ; DROP TABLE IF EXISTS CGI_ACS_TRACKING; CREATE TABLE CGI_ACS_TRACKING ( _ts taitime NOT NULL PRIMARY KEY, _ts_ TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS (tai_to_timestamp(_ts)) STORED, cgi_delta_h REAL NOT NULL, cgi_delta_v REAL NOT NULL, hv_valid_track_state_bitfield BIT(8) NOT NULL, pad_1 int GENERATED ALWAYS AS ((hv_valid_track_state_bitfield & B'11110000' >> 4)::int) STORED, cgi_hv_validity int GENERATED ALWAYS AS ((hv_valid_track_state_bitfield & B'00001100' >> 2)::int) STORED, cgi_track_state int GENERATED ALWAYS AS ((hv_valid_track_state_bitfield & B'00000011' >> 0)::int) STORED );
One is essentially an enum. I know postgres has enum types – I have other multiple other needs to query enum by index and since postgres arrays are one based indexing, I decided to do this instead of have a bunch of
i-1 in giant ENUM_RANGE expressions in other queries.
I would like to query the two tables in a way that grabs the values. The most basic possible query for this looks like
SELECT * FROM cgi_acs_tracking LEFT JOIN cgi_track_statetbl ON cgi_acs_tracking.cgi_track_state = cgi_track_statetbl.id LIMIT 1;
I would like nontechnical users to be able to do this in the GUI. I think(?) the beginning of the query is
but I can’t seem to figure out how to actually remap
cgi_track_statein the top query to the textual values pulled (val) in the bottom select.
Is the only way to do this to write the SQL query by hand in Grafana?