Build JOIN query in GUI

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_state in 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?

Hi @brandondube

Have you explored using one of Grafana’s tranformations, like outer join?

This topic was automatically closed after 365 days. New replies are no longer allowed.