Postgresql Phenotype Extract SQL issue

[kepishelf]

I had an error running the Postgresql phenotype extract sql. - Phenotype 3.1 - Using the SQL here - https://github.com/National-COVID-Cohort-Collaborative/Phenotype_Data_Acquisition/tree/master/PhenotypeScripts

Error: DatatypeMismatch: column "control_person_id" is of type integer but expression is of type text Postgres version: PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

To fix I had to perform a Cast of the NULL to become integer for the N3C_CONTROL_MAP.control_person_id

Fixed SQL

INSERT INTO results.N3C_CONTROL_MAP
    SELECT
        person_id, 1 as buddy_num, cast(null as int)
        FROM results.n3c_case_cohort
        WHERE person_id NOT IN (
            SELECT case_person_id
            FROM results.N3C_CONTROL_MAP
            WHERE buddy_num = 1
            )
        UNION
        SELECT person_id, 2 as buddy_num, cast(null as int)
        FROM results.n3c_case_cohort
        WHERE person_id NOT IN (
            SELECT case_person_id
            FROM results.N3C_CONTROL_MAP
            WHERE buddy_num = 2
            )

[burrowse]

Thanks @kepishelf we also just ran into this issue and this was helpful!

[kmkostka]

Thanks for reporting this @kepishelf . This is now resolved.