PurpleAirPSQLQueryStatements module

Copyright 2022 carlkidcrypto, All rights reserved. A file containing PSQL statements defined as constants. Generate the PSQL query strings. For simplicity our table names will match what the PurpleAir documentation says. We will do the same for table column names.

PurpleAirPSQLQueryStatements.CREATE_ENVIRONMENTAL_FIELDS_TABLE = '\n    CREATE TABLE IF NOT EXISTS environmental_fields (\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        humidity INT NULL,\n        humidity_a INT NULL,\n        humidity_b INT NULL,\n        temperature INT NULL,\n        temperature_a INT NULL,\n        temperature_b INT NULL,\n        pressure FLOAT NULL,\n        pressure_a FLOAT NULL,\n        pressure_b FLOAT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

PSQL statement for environmental_fields table

PurpleAirPSQLQueryStatements.CREATE_MISCELLANEOUS_FIELDS = '\n    CREATE TABLE IF NOT EXISTS miscellaneous_fields (\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        voc FLOAT NULL,\n        voc_a FLOAT NULL,\n        voc_b FLOAT NULL,\n        ozone1 FLOAT NULL,\n        analog_input FLOAT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

PSQL statement for miscellaneous_fields table

PurpleAirPSQLQueryStatements.CREATE_PARTICLE_COUNT_FIELDS = '\n    CREATE TABLE IF NOT EXISTS particle_count_fields (\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        um_count_0_3 FLOAT NULL,\n        um_count_a_0_3 FLOAT NULL,\n        um_count_b_0_3 FLOAT NULL,\n        um_count_0_5 FLOAT NULL,\n        um_count_a_0_5 FLOAT NULL,\n        um_count_b_0_5 FLOAT NULL,\n        um_count_1_0 FLOAT NULL,\n        um_count_a_1_0 FLOAT NULL,\n        um_count_b_1_0 FLOAT NULL,\n        um_count_2_5 FLOAT NULL,\n        um_count_a_2_5 FLOAT NULL,\n        um_count_b_2_5 FLOAT NULL,\n        um_count_5_0 FLOAT NULL,\n        um_count_a_5_0 FLOAT NULL,\n        um_count_b_5_0 FLOAT NULL,\n        um_count_10_0 FLOAT NULL,\n        um_count_a_10_0 FLOAT NULL,\n        um_count_b_10_0 FLOAT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: we can not start column names with numbers. So 0_3_um_count becomes um_count_0_3 PSQL statement for particle_count_fields table

PurpleAirPSQLQueryStatements.CREATE_PM10_0_FIELDS = '\n    CREATE TABLE IF NOT EXISTS pm10_0_fields (\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        pm10_0 FLOAT NULL,\n        pm10_0_a FLOAT NULL,\n        pm10_0_b FLOAT NULL,\n        pm10_0_atm FLOAT NULL,\n        pm10_0_atm_a FLOAT NULL,\n        pm10_0_atm_b FLOAT NULL,\n        pm10_0_cf_1 FLOAT NULL,\n        pm10_0_cf_1_a FLOAT NULL,\n        pm10_0_cf_1_b FLOAT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: Since we can’t have decimals in variable names, we do pm10_0 instead of pm10.0 PSQL statement for pm10_0_fields table

PurpleAirPSQLQueryStatements.CREATE_PM1_0_FIELDS = '\n    CREATE TABLE IF NOT EXISTS pm1_0_fields(\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        pm1_0 FLOAT NULL,\n        pm1_0_a FLOAT NULL,\n        pm1_0_b FLOAT NULL,\n        pm1_0_atm FLOAT NULL,\n        pm1_0_atm_a FLOAT NULL,\n        pm1_0_atm_b FLOAT NULL,\n        pm1_0_cf_1 FLOAT NULL,\n        pm1_0_cf_1_a FLOAT NULL,\n        pm1_0_cf_1_b FLOAT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: Since we can’t have decimals in variable names, we do pm1_0 instead of pm1.0 PSQL statement for pm1_0_fields table

PurpleAirPSQLQueryStatements.CREATE_PM2_5_FIELDS = '\n    CREATE TABLE IF NOT EXISTS pm2_5_fields (\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        pm2_5_alt FLOAT NULL,\n        pm2_5_alt_a FLOAT NULL,\n        pm2_5_alt_b FLOAT NULL,\n        pm2_5 FLOAT NULL,\n        pm2_5_a FLOAT NULL,\n        pm2_5_b FLOAT NULL,\n        pm2_5_atm FLOAT NULL,\n        pm2_5_atm_a FLOAT NULL,\n        pm2_5_atm_b FLOAT NULL,\n        pm2_5_cf_1 FLOAT NULL,\n        pm2_5_cf_1_a FLOAT NULL,\n        pm2_5_cf_1_b FLOAT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: Since we can’t have decimals in variable names, we do pm2_5 instead of pm2.5 PSQL statement for pm2_5_fields table

PurpleAirPSQLQueryStatements.CREATE_PM2_5_PSEUDO_AVERAGE_FIELDS = '\n    CREATE TABLE IF NOT EXISTS pm2_5_pseudo_average_fields (\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        pm2_5_10minute FLOAT NULL,\n        pm2_5_10minute_a FLOAT NULL,\n        pm2_5_10minute_b FLOAT NULL,\n        pm2_5_30minute FLOAT NULL,\n        pm2_5_30minute_a FLOAT NULL,\n        pm2_5_30minute_b FLOAT NULL,\n        pm2_5_60minute FLOAT NULL,\n        pm2_5_60minute_a FLOAT NULL,\n        pm2_5_60minute_b FLOAT NULL,\n        pm2_5_6hour FLOAT NULL,\n        pm2_5_6hour_a FLOAT NULL,\n        pm2_5_6hour_b FLOAT NULL,\n        pm2_5_24hour FLOAT NULL,\n        pm2_5_24hour_a FLOAT NULL,\n        pm2_5_24hour_b FLOAT NULL,\n        pm2_5_1week FLOAT NULL,\n        pm2_5_1week_a FLOAT NULL,\n        pm2_5_1week_b FLOAT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: Since we can’t have decimals in variable names, we do pm2_5 instead of pm2.5 PSQL statement for pm2_5_pseudo_average_fields table

PurpleAirPSQLQueryStatements.CREATE_STATION_INFORMATION_AND_STATUS_FIELDS_TABLE = '\n    CREATE TABLE IF NOT EXISTS station_information_and_status_fields (\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        name TEXT NULL,\n        icon INT NULL,\n        model TEXT NULL,\n        hardware TEXT NULL,\n        location_type INT NULL,\n        private INT NULL,\n        latitude FLOAT NULL,\n        longitude FLOAT NULL,\n        altitude FLOAT NULL,\n        position_rating INT NULL,\n        led_brightness INT NULL,\n        firmware_version TEXT NULL,\n        firmware_upgrade TEXT NULL,\n        rssi INT NULL,\n        uptime INT NULL,\n        pa_latency INT NULL,\n        memory INT NULL,\n        last_seen TIMESTAMPTZ NULL,\n        last_modified TIMESTAMPTZ NULL,\n        date_created TIMESTAMPTZ NULL,\n        channel_state INT NULL,\n        channel_flags INT NULL,\n        channel_flags_manual INT NULL,\n        channel_flags_auto INT NULL,\n        confidence INT NULL,\n        confidence_manual INT NULL,\n        confidence_auto INT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

PSQL statement for station_information_and_status_fields table

PurpleAirPSQLQueryStatements.CREATE_THINGSPEAK_FIELDS = '\n    CREATE TABLE IF NOT EXISTS thingspeak_fields (\n        data_time_stamp TIMESTAMPTZ NOT NULL,\n        sensor_index INT NOT NULL,\n        primary_id_a INT NULL,\n        primary_key_a TEXT NULL,\n        secondary_id_a INT NULL,\n        secondary_key_a TEXT NULL,\n        primary_id_b INT NULL,\n        primary_key_b TEXT NULL,\n        secondary_id_b INT NULL,\n        secondary_key_b TEXT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note TO SELF MAY END UP GETTING RID OF THIS TABLE. I SEE NO USE FOR IT. PSQL statement for thingspeak_fields table

PurpleAirPSQLQueryStatements.PSQL_CREATE_CONTINUOUS_AGGREGATE_POLICY_ON_SENSOR_INDEX_AND_NAME_1HOUR_AGGREGATE = "\n    SELECT add_continuous_aggregate_policy('sensor_index_and_name_1hour_aggregate',\n    start_offset => INTERVAL '3 h',\n    end_offset => INTERVAL '1 h',\n    schedule_interval => INTERVAL '1 h',\n    if_not_exists => true);\n    "

PSQL statement to add a TimescaleDB continuous refresh policy on the sensor_index_and_name_1hour_aggregate materialized view Documentation can be found here: https://docs.timescale.com/timescaledb/latest/how-to-guides/continuous-aggregates/refresh-policies/

PurpleAirPSQLQueryStatements.PSQL_CREATE_DATA_RETENTION_POLICY_ON_SENSOR_INDEX_AND_NAME_1HOUR_AGGREGATE = "\n    SELECT add_retention_policy('sensor_index_and_name_1hour_aggregate',\n    INTERVAL '8 hours',\n    if_not_exists => true); \n    "

PSQL statement to add a TimescaleDB data retention policy on the sensor_index_and_name_1hour_aggregate materialized view Documentation can be found here: https://docs.timescale.com/timescaledb/latest/how-to-guides/data-retention/data-retention-with-continuous-aggregates/

PurpleAirPSQLQueryStatements.PSQL_CREATE_MATERIALIZED_VIEW_SENSOR_INDEX_AND_NAME_1HOUR_AGGREGATE = "\n    CREATE MATERIALIZED VIEW IF NOT EXISTS sensor_index_and_name_1hour_aggregate(data_time_stamp, sensor_index, name)\n    WITH (timescaledb.continuous) AS\n\t    SELECT time_bucket('1h', data_time_stamp), sensor_index, name\n\t    FROM station_information_and_status_fields\n\t    GROUP BY time_bucket('1h', data_time_stamp), sensor_index, name\n    WITH NO DATA;\n    "

PSQL statement to create a TimescaleDB Materialized View Documentation can be found here: https://docs.timescale.com/api/latest/continuous-aggregates/create_materialized_view/

PurpleAirPSQLQueryStatements.PSQL_DROP_ALL_TABLES = '\n    DROP TABLE station_information_and_status_fields CASCADE;\n    DROP TABLE environmental_fields CASCADE;\n    DROP TABLE miscellaneous_fields CASCADE;\n    DROP TABLE pm1_0_fields CASCADE;\n    DROP TABLE pm2_5_fields CASCADE;\n    DROP TABLE pm2_5_pseudo_average_fields CASCADE;\n    DROP TABLE pm10_0_fields CASCADE;\n    DROP TABLE particle_count_fields CASCADE;\n    DROP TABLE thingspeak_fields CASCADE;\n    '

PSQL statement to drop all tables in the database

PurpleAirPSQLQueryStatements.PSQL_GET_LIST_OF_ACTIVE_COMPRESSION_POLICIES = "\n    SELECT hypertable_name FROM timescaledb_information.jobs\n    WHERE proc_name='policy_compression';\n    "

PSQL statement to see active TimescaleDB compression policies Documentation can be found here: https://docs.timescale.com/timescaledb/latest/how-to-guides/compression/about-compression/

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_ENVIRONMENTAL_FIELDS = '\n    INSERT INTO environmental_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            humidity,\n            humidity_a,\n            humidity_b,\n            temperature,\n            temperature_a,\n            temperature_b,\n            pressure,\n            pressure_a,\n            pressure_b\n        ) \n        VALUES\n        (\n            CAST(:data_time_stamp AS TIMESTAMPTZ),\n            CAST(:sensor_index AS INT),\n            CAST(:humidity AS INT),\n            CAST(:humidity_a AS INT),\n            CAST(:humidity_b AS INT),\n            CAST(:temperature AS INT),\n            CAST(:temperature_a AS INT),\n            CAST(:temperature_b AS INT),\n            CAST(:pressure AS FLOAT),\n            CAST(:pressure_a AS FLOAT),\n            CAST(:pressure_b AS FLOAT)\n        )'

PSQL insert statement for environmental_fields

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_MISCELLANEOUS_FIELDS = '\n    INSERT INTO miscellaneous_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            voc,\n            voc_a,\n            voc_b,\n            ozone1,\n            analog_input\n        ) \n        VALUES\n        (\n            CAST(:data_time_stamp AS TIMESTAMPTZ),\n            CAST(:sensor_index AS INT),\n            CAST(:voc AS FLOAT),\n            CAST(:voc_a AS FLOAT),\n            CAST(:voc_b AS FLOAT),\n            CAST(:ozone1 AS FLOAT),\n            CAST(:analog_input AS FLOAT)\n        )'

PSQL insert statement for miscellaneous_fields

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_PARTICLE_COUNT_FIELDS = '\n    INSERT INTO particle_count_fields \n    (\n        data_time_stamp,\n        sensor_index,\n        um_count_0_3,\n        um_count_a_0_3,\n        um_count_b_0_3,\n        um_count_0_5,\n        um_count_a_0_5,\n        um_count_b_0_5,\n        um_count_1_0,\n        um_count_a_1_0,\n        um_count_b_1_0,\n        um_count_2_5,\n        um_count_a_2_5,\n        um_count_b_2_5,\n        um_count_5_0,\n        um_count_a_5_0,\n        um_count_b_5_0,\n        um_count_10_0,\n        um_count_a_10_0,\n        um_count_b_10_0\n    )\n    VALUES\n    (\n        CAST(:data_time_stamp AS TIMESTAMPTZ),\n        CAST(:sensor_index AS INT),\n        CAST(:um_count_0_3 AS FLOAT),\n        CAST(:um_count_a_0_3 AS FLOAT),\n        CAST(:um_count_b_0_3 AS FLOAT),\n        CAST(:um_count_0_5 AS FLOAT),\n        CAST(:um_count_a_0_5 AS FLOAT),\n        CAST(:um_count_b_0_5 AS FLOAT),\n        CAST(:um_count_1_0 AS FLOAT),\n        CAST(:um_count_a_1_0 AS FLOAT),\n        CAST(:um_count_b_1_0 AS FLOAT),\n        CAST(:um_count_2_5 AS FLOAT),\n        CAST(:um_count_a_2_5 AS FLOAT),\n        CAST(:um_count_b_2_5 AS FLOAT),\n        CAST(:um_count_5_0 AS FLOAT),\n        CAST(:um_count_a_5_0 AS FLOAT),\n        CAST(:um_count_b_5_0 AS FLOAT),\n        CAST(:um_count_10_0 AS FLOAT),\n        CAST(:um_count_a_10_0 AS FLOAT),\n        CAST(:um_count_b_10_0 AS FLOAT)\n    )'

PSQL insert statement for particle_count_fields

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_PM10_0_FIELDS = '\n    INSERT INTO pm10_0_fields\n    (\n        data_time_stamp,\n        sensor_index,\n        pm10_0,\n        pm10_0_a,\n        pm10_0_b,\n        pm10_0_atm,\n        pm10_0_atm_a,\n        pm10_0_atm_b,\n        pm10_0_cf_1,\n        pm10_0_cf_1_a,\n        pm10_0_cf_1_b\n    ) \n    VALUES\n    (\n        CAST(:data_time_stamp AS TIMESTAMPTZ),\n        CAST(:sensor_index AS INT),\n        CAST(:pm10_0 AS FLOAT),\n        CAST(:pm10_0_a AS FLOAT),\n        CAST(:pm10_0_b AS FLOAT),\n        CAST(:pm10_0_atm AS FLOAT),\n        CAST(:pm10_0_atm_a AS FLOAT),\n        CAST(:pm10_0_atm_b AS FLOAT),\n        CAST(:pm10_0_cf_1 AS FLOAT),\n        CAST(:pm10_0_cf_1_a AS FLOAT),\n        CAST(:pm10_0_cf_1_b AS FLOAT)\n    )'

PSQL insert statement for pm10_0_fields

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_PM1_0_FIELDS = '\n    INSERT INTO pm1_0_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            pm1_0,\n            pm1_0_a,\n            pm1_0_b,\n            pm1_0_atm,\n            pm1_0_atm_a,\n            pm1_0_atm_b,\n            pm1_0_cf_1,\n            pm1_0_cf_1_a,\n            pm1_0_cf_1_b\n        )\n        VALUES\n        (\n            CAST(:data_time_stamp AS TIMESTAMPTZ),\n            CAST(:sensor_index AS INT),\n            CAST(:pm1_0 AS FLOAT),\n            CAST(:pm1_0_a AS FLOAT),\n            CAST(:pm1_0_b AS FLOAT),\n            CAST(:pm1_0_atm AS FLOAT),\n            CAST(:pm1_0_atm_a AS FLOAT),\n            CAST(:pm1_0_atm_b AS FLOAT),\n            CAST(:pm1_0_cf_1 AS FLOAT),\n            CAST(:pm1_0_cf_1_a AS FLOAT),\n            CAST(:pm1_0_cf_1_b AS FLOAT)\n        )'

PSQL insert statement for pm1_0_fields

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_PM2_5_FIELDS = '\n    INSERT INTO pm2_5_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            pm2_5_alt,\n            pm2_5_alt_a,\n            pm2_5_alt_b,\n            pm2_5,\n            pm2_5_a,\n            pm2_5_b,\n            pm2_5_atm,\n            pm2_5_atm_a,\n            pm2_5_atm_b,\n            pm2_5_cf_1,\n            pm2_5_cf_1_a,\n            pm2_5_cf_1_b\n        ) \n        VALUES\n        (\n            CAST(:data_time_stamp AS TIMESTAMPTZ),\n            CAST(:sensor_index AS INT),\n            CAST(:pm2_5_alt AS FLOAT),\n            CAST(:pm2_5_alt_a AS FLOAT),\n            CAST(:pm2_5_alt_b AS FLOAT),\n            CAST(:pm2_5 AS FLOAT),\n            CAST(:pm2_5_a AS FLOAT),\n            CAST(:pm2_5_b AS FLOAT),\n            CAST(:pm2_5_atm AS FLOAT),\n            CAST(:pm2_5_atm_a AS FLOAT),\n            CAST(:pm2_5_atm_b AS FLOAT),\n            CAST(:pm2_5_cf_1 AS FLOAT),\n            CAST(:pm2_5_cf_1_a AS FLOAT),\n            CAST(:pm2_5_cf_1_b AS FLOAT)\n        )'

PSQL insert statement for pm2_5_fields

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_PM2_5_PSEUDO_AVERAGE_FIELDS = '\n    INSERT INTO pm2_5_pseudo_average_fields \n    (\n        data_time_stamp,\n        sensor_index,\n        pm2_5_10minute,\n        pm2_5_10minute_a,\n        pm2_5_10minute_b,\n        pm2_5_30minute,\n        pm2_5_30minute_a,\n        pm2_5_30minute_b,\n        pm2_5_60minute,\n        pm2_5_60minute_a,\n        pm2_5_60minute_b,\n        pm2_5_6hour,\n        pm2_5_6hour_a,\n        pm2_5_6hour_b,\n        pm2_5_24hour,\n        pm2_5_24hour_a,\n        pm2_5_24hour_b,\n        pm2_5_1week,\n        pm2_5_1week_a,\n        pm2_5_1week_b\n    )\n    VALUES\n    (\n        CAST(:data_time_stamp AS TIMESTAMPTZ),\n        CAST(:sensor_index AS INT),\n        CAST(:pm2_5_10minute AS FLOAT),\n        CAST(:pm2_5_10minute_a AS FLOAT),\n        CAST(:pm2_5_10minute_b AS FLOAT),\n        CAST(:pm2_5_30minute AS FLOAT),\n        CAST(:pm2_5_30minute_a AS FLOAT),\n        CAST(:pm2_5_30minute_b AS FLOAT),\n        CAST(:pm2_5_60minute AS FLOAT),\n        CAST(:pm2_5_60minute_a AS FLOAT),\n        CAST(:pm2_5_60minute_b AS FLOAT),\n        CAST(:pm2_5_6hour AS FLOAT),\n        CAST(:pm2_5_6hour_a AS FLOAT),\n        CAST(:pm2_5_6hour_b AS FLOAT),\n        CAST(:pm2_5_24hour AS FLOAT),\n        CAST(:pm2_5_24hour_a AS FLOAT),\n        CAST(:pm2_5_24hour_b AS FLOAT),\n        CAST(:pm2_5_1week AS FLOAT),\n        CAST(:pm2_5_1week_a AS FLOAT),\n        CAST(:pm2_5_1week_b AS FLOAT)\n    )'

PSQL insert statement for pm2_5_pseudo_average_fields

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_STATION_INFORMATION_AND_STATUS_FIELDS = '\n    INSERT INTO station_information_and_status_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            name,\n            icon,\n            model,\n            hardware,\n            location_type,\n            private,\n            latitude,\n            longitude,\n            altitude,\n            position_rating,\n            led_brightness,\n            firmware_version,\n            firmware_upgrade,\n            rssi,\n            uptime,\n            pa_latency,\n            memory,\n            last_seen,\n            last_modified,\n            date_created,\n            channel_state,\n            channel_flags,\n            channel_flags_manual,\n            channel_flags_auto,\n            confidence,\n            confidence_manual,\n            confidence_auto\n        )\n        VALUES \n        (\n            CAST(:data_time_stamp AS TIMESTAMPTZ),\n            CAST(:sensor_index AS INT),\n            CAST(:name AS TEXT),\n            CAST(:icon AS INT),\n            CAST(:model AS TEXT),\n            CAST(:hardware AS TEXT),\n            CAST(:location_type AS INT),\n            CAST(:private AS INT),\n            CAST(:latitude AS FLOAT),\n            CAST(:longitude AS FLOAT),\n            CAST(:altitude AS FLOAT),\n            CAST(:position_rating AS INT),\n            CAST(:led_brightness AS INT),\n            CAST(:firmware_version AS TEXT),\n            CAST(:firmware_upgrade AS TEXT),\n            CAST(:rssi AS INT),\n            CAST(:uptime AS INT),\n            CAST(:pa_latency AS INT),\n            CAST(:memory AS INT),\n            CAST(:last_seen AS TIMESTAMPTZ),\n            CAST(:last_modified AS TIMESTAMPTZ),\n            CAST(:date_created AS TIMESTAMPTZ),\n            CAST(:channel_state AS INT),\n            CAST(:channel_flags AS INT),\n            CAST(:channel_flags_manual AS INT),\n            CAST(:channel_flags_auto AS INT),\n            CAST(:confidence AS INT),\n            CAST(:confidence_manual AS INT),\n            CAST(:confidence_auto AS INT)\n        )'

PSQL insert statement for station_information_and_status_fields

PurpleAirPSQLQueryStatements.PSQL_INSERT_STATEMENT_THINGSPEAK_FIELDS = '\n    INSERT INTO thingspeak_fields\n    (\n        data_time_stamp,\n        sensor_index,\n        primary_id_a,\n        primary_key_a,\n        secondary_id_a,\n        secondary_key_a,\n        primary_id_b,\n        primary_key_b,\n        secondary_id_b,\n        secondary_key_b\n    )\n    VALUES\n    (\n        CAST(:data_time_stamp AS TIMESTAMPTZ),\n        CAST(:sensor_index AS INT),\n        CAST(:primary_id_a AS INT),\n        CAST(:primary_key_a AS TEXT),\n        CAST(:secondary_id_a AS INT),\n        CAST(:secondary_key_a AS TEXT),\n        CAST(:primary_id_b AS INT),\n        CAST(:primary_key_b AS TEXT),\n        CAST(:secondary_id_b AS INT),\n        CAST(:secondary_key_b AS TEXT)\n    )'

PSQL insert statement for thingspeak_fields