REFRESH MATERIALIZED VIEW core.mv_subject_form WITH DATA;

Date: 2025-12-09 21:54:03
Duration: 1s707ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: [unknown]
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

REFRESH MATERIALIZED VIEW core.mv_subject_form WITH DATA;

Date: 2025-12-09 07:30:03
Duration: 1s704ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: [unknown]
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

REFRESH MATERIALIZED VIEW core.mv_subject_form WITH DATA;

Date: 2025-12-09 22:54:03
Duration: 1s640ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: [unknown]
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

x Hide

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject_inform_consent"} */ ALTER TABLE "celspac"."dbt_stg"."stg_celspac_admin__subject_inform_consent" ADD PRIMARY KEY (id);

Date: 2025-12-09 05:02:04
Duration: 149ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject_inform_consent"} */ ALTER TABLE "celspac"."dbt_stg"."stg_celspac_admin__subject_inform_consent" ADD PRIMARY KEY (id);

Date: 2025-12-09 17:02:04
Duration: 135ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject_inform_consent"} */ ALTER TABLE "celspac"."dbt_stg"."stg_celspac_admin__subject_inform_consent" ADD PRIMARY KEY (id);

Date: 2025-12-09 05:02:04
Duration: 0ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt

x Hide

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject"} */ ALTER TABLE "celspac"."dbt_stg"."stg_celspac_admin__subject" ADD PRIMARY KEY (id);

Date: 2025-12-09 17:02:19
Duration: 31ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject"} */ ALTER TABLE "celspac"."dbt_stg"."stg_celspac_admin__subject" ADD PRIMARY KEY (id);

Date: 2025-12-09 05:02:18
Duration: 27ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject"} */ ALTER TABLE "celspac"."dbt_stg"."stg_celspac_admin__subject" ADD PRIMARY KEY (id);

Date: 2025-12-09 05:02:18
Duration: 0ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt

x Hide

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject"} */ create table "celspac"."dbt_stg"."stg_celspac_admin__subject__dbt_tmp" as ( WITH ranked_table_con AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY subject_id, type_code ORDER BY priority DESC ) AS ranked_row FROM "celspac"."core"."contact" WHERE value -> 'permanent' = 'true'), ranked_table_contact AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY subject_id, type_code ORDER BY priority DESC ) AS ranked_row FROM "celspac"."core"."contact"), max_prio_perm AS ( SELECT * FROM ranked_table_con WHERE ranked_row = 1), contact_table_max_prio AS ( SELECT * FROM ranked_table_contact WHERE ranked_row = 1), contact_d AS ( SELECT DISTINCT subject_id, replace((value -> 'municipality')::text, '"', '' ) AS city, replace((value -> 'postalcode')::text, '"', '' ) AS postal_code, replace((value -> 'country')::text, '"', '' ) AS country, replace((value -> 'streetname')::text, '"', '' ) AS street, replace((value -> 'streetnumber')::text, '"', '' ) AS street_number, replace((value -> 'permanent')::text, '"', '' ) AS permanent FROM max_prio_perm con WHERE con.type_code = 'ADDRESS'), contact_d_c AS ( SELECT DISTINCT subject_id, replace((value -> 'municipality')::text, '"', '' ) AS city, replace((value -> 'postalcode')::text, '"', '' ) AS postal_code, replace((value -> 'country')::text, '"', '' ) AS country, replace((value -> 'streetname')::text, '"', '' ) AS street, replace((value -> 'streetnumber')::text, '"', '' ) AS street_number, replace((value -> 'permanent')::text, '"', '' ) AS permanent FROM contact_table_max_prio con WHERE con.type_code = 'ADDRESS'), con_email AS ( SELECT subject_id, replace(value::text, '"', '' ) AS email FROM contact_table_max_prio con WHERE con.type_code = 'EMAIL'), con_ph AS ( SELECT subject_id, replace(value::text, '"', '' ) AS phone_number FROM contact_table_max_prio WHERE type_code = 'PHONE' ) SELECT s.id, s.subject_state_code, s.sex_code, s.family_id, s.elspac_family_relationship_type_code, s.is_origin_elspac, s.family_code, s.owner_user_id, s.owner_group_id, s.name, s.surname, s.titles_before, s.titles_after, s.description, s.rc, s.tng_id, s.puk, s.birthdate, s_dead.death_by, s_dead.death_at, s_dead.date_of_death, s_dead.cause_of_death, c_e.email, c_p.phone_number, con_d.city, con_d.street, con_d.street_number AS street_number, con_d.postal_code, con_d.country, con_d.permanent, con_d_c.street AS street_contact, con_d_c.street_number AS street_number_contact, con_d_c.city AS city_contact, con_d_c.postal_code AS postal_code_contact, con_d_c.country as country_contact, s.is_archived, s.user_id, s.maiden_name, s.test_proband, s.created_by, s.enrolled_by, s.created_at, s.enrolled_at, s.anonym_by, s.anonym_at, se.* FROM "celspac"."core"."subject" s LEFT JOIN "celspac"."dbt_stg"."inte_unique_subject_extend_columns" se ON se.subject_id = s.id LEFT JOIN "celspac"."core"."subject_dead" s_dead ON s.id = s_dead.subject_id LEFT JOIN contact_d con_d ON con_d.subject_id = s.id LEFT JOIN contact_d_c con_d_c ON con_d_c.subject_id = s.id LEFT JOIN con_email c_e ON c_e.subject_id = s.id LEFT JOIN con_ph c_p ON c_p.subject_id = s.id );

Date: 2025-12-09 05:02:18
Duration: 1s260ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject"} */ create table "celspac"."dbt_stg"."stg_celspac_admin__subject__dbt_tmp" as ( WITH ranked_table_con AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY subject_id, type_code ORDER BY priority DESC ) AS ranked_row FROM "celspac"."core"."contact" WHERE value -> 'permanent' = 'true'), ranked_table_contact AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY subject_id, type_code ORDER BY priority DESC ) AS ranked_row FROM "celspac"."core"."contact"), max_prio_perm AS ( SELECT * FROM ranked_table_con WHERE ranked_row = 1), contact_table_max_prio AS ( SELECT * FROM ranked_table_contact WHERE ranked_row = 1), contact_d AS ( SELECT DISTINCT subject_id, replace((value -> 'municipality')::text, '"', '' ) AS city, replace((value -> 'postalcode')::text, '"', '' ) AS postal_code, replace((value -> 'country')::text, '"', '' ) AS country, replace((value -> 'streetname')::text, '"', '' ) AS street, replace((value -> 'streetnumber')::text, '"', '' ) AS street_number, replace((value -> 'permanent')::text, '"', '' ) AS permanent FROM max_prio_perm con WHERE con.type_code = 'ADDRESS'), contact_d_c AS ( SELECT DISTINCT subject_id, replace((value -> 'municipality')::text, '"', '' ) AS city, replace((value -> 'postalcode')::text, '"', '' ) AS postal_code, replace((value -> 'country')::text, '"', '' ) AS country, replace((value -> 'streetname')::text, '"', '' ) AS street, replace((value -> 'streetnumber')::text, '"', '' ) AS street_number, replace((value -> 'permanent')::text, '"', '' ) AS permanent FROM contact_table_max_prio con WHERE con.type_code = 'ADDRESS'), con_email AS ( SELECT subject_id, replace(value::text, '"', '' ) AS email FROM contact_table_max_prio con WHERE con.type_code = 'EMAIL'), con_ph AS ( SELECT subject_id, replace(value::text, '"', '' ) AS phone_number FROM contact_table_max_prio WHERE type_code = 'PHONE' ) SELECT s.id, s.subject_state_code, s.sex_code, s.family_id, s.elspac_family_relationship_type_code, s.is_origin_elspac, s.family_code, s.owner_user_id, s.owner_group_id, s.name, s.surname, s.titles_before, s.titles_after, s.description, s.rc, s.tng_id, s.puk, s.birthdate, s_dead.death_by, s_dead.death_at, s_dead.date_of_death, s_dead.cause_of_death, c_e.email, c_p.phone_number, con_d.city, con_d.street, con_d.street_number AS street_number, con_d.postal_code, con_d.country, con_d.permanent, con_d_c.street AS street_contact, con_d_c.street_number AS street_number_contact, con_d_c.city AS city_contact, con_d_c.postal_code AS postal_code_contact, con_d_c.country as country_contact, s.is_archived, s.user_id, s.maiden_name, s.test_proband, s.created_by, s.enrolled_by, s.created_at, s.enrolled_at, s.anonym_by, s.anonym_at, se.* FROM "celspac"."core"."subject" s LEFT JOIN "celspac"."dbt_stg"."inte_unique_subject_extend_columns" se ON se.subject_id = s.id LEFT JOIN "celspac"."core"."subject_dead" s_dead ON s.id = s_dead.subject_id LEFT JOIN contact_d con_d ON con_d.subject_id = s.id LEFT JOIN contact_d_c con_d_c ON con_d_c.subject_id = s.id LEFT JOIN con_email c_e ON c_e.subject_id = s.id LEFT JOIN con_ph c_p ON c_p.subject_id = s.id );

Date: 2025-12-09 17:02:19
Duration: 1s243ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.stg_celspac_admin__subject"} */ create table "celspac"."dbt_stg"."stg_celspac_admin__subject__dbt_tmp" as ( WITH ranked_table_con AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY subject_id, type_code ORDER BY priority DESC ) AS ranked_row FROM "celspac"."core"."contact" WHERE value -> 'permanent' = 'true'), ranked_table_contact AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY subject_id, type_code ORDER BY priority DESC ) AS ranked_row FROM "celspac"."core"."contact"), max_prio_perm AS ( SELECT * FROM ranked_table_con WHERE ranked_row = 1), contact_table_max_prio AS ( SELECT * FROM ranked_table_contact WHERE ranked_row = 1), contact_d AS ( SELECT DISTINCT subject_id, replace((value -> 'municipality')::text, '"', '' ) AS city, replace((value -> 'postalcode')::text, '"', '' ) AS postal_code, replace((value -> 'country')::text, '"', '' ) AS country, replace((value -> 'streetname')::text, '"', '' ) AS street, replace((value -> 'streetnumber')::text, '"', '' ) AS street_number, replace((value -> 'permanent')::text, '"', '' ) AS permanent FROM max_prio_perm con WHERE con.type_code = 'ADDRESS'), contact_d_c AS ( SELECT DISTINCT subject_id, replace((value -> 'municipality')::text, '"', '' ) AS city, replace((value -> 'postalcode')::text, '"', '' ) AS postal_code, replace((value -> 'country')::text, '"', '' ) AS country, replace((value -> 'streetname')::text, '"', '' ) AS street, replace((value -> 'streetnumber')::text, '"', '' ) AS street_number, replace((value -> 'permanent')::text, '"', '' ) AS permanent FROM contact_table_max_prio con WHERE con.type_code = 'ADDRESS'), con_email AS ( SELECT subject_id, replace(value::text, '"', '' ) AS email FROM contact_table_max_prio con WHERE con.type_code = 'EMAIL'), con_ph AS ( SELECT subject_id, replace(value::text, '"', '' ) AS phone_number FROM contact_table_max_prio WHERE type_code = 'PHONE' ) SELECT s.id, s.subject_state_code, s.sex_code, s.family_id, s.elspac_family_relationship_type_code, s.is_origin_elspac, s.family_code, s.owner_user_id, s.owner_group_id, s.name, s.surname, s.titles_before, s.titles_after, s.description, s.rc, s.tng_id, s.puk, s.birthdate, s_dead.death_by, s_dead.death_at, s_dead.date_of_death, s_dead.cause_of_death, c_e.email, c_p.phone_number, con_d.city, con_d.street, con_d.street_number AS street_number, con_d.postal_code, con_d.country, con_d.permanent, con_d_c.street AS street_contact, con_d_c.street_number AS street_number_contact, con_d_c.city AS city_contact, con_d_c.postal_code AS postal_code_contact, con_d_c.country as country_contact, s.is_archived, s.user_id, s.maiden_name, s.test_proband, s.created_by, s.enrolled_by, s.created_at, s.enrolled_at, s.anonym_by, s.anonym_at, se.* FROM "celspac"."core"."subject" s LEFT JOIN "celspac"."dbt_stg"."inte_unique_subject_extend_columns" se ON se.subject_id = s.id LEFT JOIN "celspac"."core"."subject_dead" s_dead ON s.id = s_dead.subject_id LEFT JOIN contact_d con_d ON con_d.subject_id = s.id LEFT JOIN contact_d_c con_d_c ON con_d_c.subject_id = s.id LEFT JOIN con_email c_e ON c_e.subject_id = s.id LEFT JOIN con_ph c_p ON c_p.subject_id = s.id );

Date: 2025-12-09 05:02:18
Duration: 0ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt

x Hide

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.inte_unique_subject_extend_array"} */ create table "celspac"."dbt_stg"."inte_unique_subject_extend_array__dbt_tmp" as ( SELECT subject_id, array_agg(extend_template ORDER BY subcohort_id ) AS extend_template, array_agg(extend ORDER BY subcohort_id ) AS extend, array_agg(description ORDER BY subcohort_id ) AS description, array_agg(description_internal ORDER BY subcohort_id ) AS description_internal, array_agg(subcohort_id ORDER BY subcohort_id ) AS subcohort_id, array_agg(subcohort_name ORDER BY subcohort_id ) AS subcohort_name, array_agg(column_prefix ORDER BY subcohort_id ) AS column_prefix FROM "celspac"."dbt_stg"."inte_subject_extend" GROUP BY subject_id );

Date: 2025-12-09 05:02:04
Duration: 502ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.inte_unique_subject_extend_array"} */ create table "celspac"."dbt_stg"."inte_unique_subject_extend_array__dbt_tmp" as ( SELECT subject_id, array_agg(extend_template ORDER BY subcohort_id ) AS extend_template, array_agg(extend ORDER BY subcohort_id ) AS extend, array_agg(description ORDER BY subcohort_id ) AS description, array_agg(description_internal ORDER BY subcohort_id ) AS description_internal, array_agg(subcohort_id ORDER BY subcohort_id ) AS subcohort_id, array_agg(subcohort_name ORDER BY subcohort_id ) AS subcohort_name, array_agg(column_prefix ORDER BY subcohort_id ) AS column_prefix FROM "celspac"."dbt_stg"."inte_subject_extend" GROUP BY subject_id );

Date: 2025-12-09 17:02:04
Duration: 450ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.inte_unique_subject_extend_array"} */ create table "celspac"."dbt_stg"."inte_unique_subject_extend_array__dbt_tmp" as ( SELECT subject_id, array_agg(extend_template ORDER BY subcohort_id ) AS extend_template, array_agg(extend ORDER BY subcohort_id ) AS extend, array_agg(description ORDER BY subcohort_id ) AS description, array_agg(description_internal ORDER BY subcohort_id ) AS description_internal, array_agg(subcohort_id ORDER BY subcohort_id ) AS subcohort_id, array_agg(subcohort_name ORDER BY subcohort_id ) AS subcohort_name, array_agg(column_prefix ORDER BY subcohort_id ) AS column_prefix FROM "celspac"."dbt_stg"."inte_subject_extend" GROUP BY subject_id );

Date: 2025-12-09 05:02:04
Duration: 0ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt

x Hide

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.export_proband_by_pilot__19"} */ create table "celspac"."dbt_marts_dbt"."export_proband_by_pilot__19__dbt_tmp" as ( -- suject (proband) patrici do kohorty COVID-19: Imunita, Pilot ID: 19 -- WITH proband_subcohort_role AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, subject_role_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS role ("subject_id" int, "covid_p01__role" text, "covid_p02__role" text, "covid_p03__role" text)), proband_subcohort_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, subject_inform_consent_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS role ("subject_id" int, "covid_p01__state" text, "covid_p02__state" text, "covid_p03__state" text)), proband_subcohort_activity_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, activity_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS activity ("subject_id" int, "covid_p01__activity_state" text, "covid_p02__activity_state" text, "covid_p03__activity_state" text)), proband_subcohort_created_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, created_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS created_at ("subject_id" int, "covid_p01__created_at" text, "covid_p02__created_at" text, "covid_p03__created_at" text)), proband_subcohort_accepted_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, accepted_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS accepted_at ("subject_id" int, "covid_p01__accepted_at" text, "covid_p02__accepted_at" text, "covid_p03__accepted_at" text)), proband_subcohort_cancelled_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, cancelled_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS cancelled_at ("subject_id" int, "covid_p01__cancelled_at" text, "covid_p02__cancelled_at" text, "covid_p03__cancelled_at" text)), proband_subcohort_revoked_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, revoked_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS revoked_at ("subject_id" int, "covid_p01__revoked_at" text, "covid_p02__revoked_at" text, "covid_p03__revoked_at" text)), proband_subcohort_paused_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, paused_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS paused_at ("subject_id" int, "covid_p01__paused_at" text, "covid_p02__paused_at" text, "covid_p03__paused_at" text)), proband_ic_type_biomaterial_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, label, subject_inform_consent_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__ict_biomaterial_base_data" WHERE pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01_ic_venous_blood_proband'), ('covid_p02_ic_venous_blood_proband'), ('covid_p03_ic_venous_blood_proband') $$ ) AS biomaterial_state ("subject_id" int, "covid_p01_ic_venous_blood_proband" text, "covid_p02_ic_venous_blood_proband" text, "covid_p03_ic_venous_blood_proband" text) ) SELECT sbj.tng_id AS proband_id, sbj.test_proband AS is_test, sbj.family_id, sbj.family_code, sbj.elspac_family_relationship_type_code, sbj.subject_state_code, sbj.rc, sbj.sex_code, sbj.birthdate, sbj.death_at, sbj.date_of_death, sbj.cause_of_death, sbj.titles_before, sbj.name, sbj.surname, sbj.titles_after, sbj.email, sbj.phone_number, sbj.street, sbj.street_number, sbj.city, sbj.postal_code, sbj.country, sbj.street_contact, sbj.street_number_contact, sbj.city_contact, sbj.postal_code_contact, sbj.country_contact, sbj.is_origin_elspac, sbj.description, sbj.tng_p08__description, sbj.tng_p08__description_internal, sbj.tng_p08__expectedChildbirth, sbj.tng_p08__childbirth, sbj.tng_p08__location, sbj.tng_p08__hospitalId, sbj.tng_p08__childId, sbj.tng_p08__childCode, sbj.tng_p01__description, sbj.tng_p01__description_internal, sbj.tng_p01__expectedChildbirth, sbj.tng_p01__childbirth, sbj.tng_p01__location, sbj.tng_p01__hospitalId, sbj.tng_p01__childId, sbj.tng_p01__childCode, sbj.tng_p02__description, sbj.tng_p02__description_internal, sbj.tng_p02__expectedChildbirth, sbj.tng_p02__childbirth, sbj.tng_p02__location, sbj.tng_p02__hospitalId, sbj.tng_p02__childId, sbj.tng_p02__childCode, sbj.tng_p03__description, sbj.tng_p03__description_internal, sbj.tng_p03__expectedChildbirth, sbj.tng_p03__childbirth, sbj.tng_p03__location, sbj.tng_p03__hospitalId, sbj.tng_p03__childId, sbj.tng_p03__childCode, sbj.tng_p04__description, sbj.tng_p04__description_internal, sbj.tng_p04__expectedChildbirth, sbj.tng_p04__childbirth, sbj.tng_p04__location, sbj.tng_p04__hospitalId, sbj.tng_p04__childId, sbj.tng_p04__childCode, sbj.tng_p05__description, sbj.tng_p05__description_internal, sbj.tng_p05__expectedChildbirth, sbj.tng_p05__childbirth, sbj.tng_p05__location, sbj.tng_p05__hospitalId, sbj.tng_p05__childId, sbj.tng_p05__childCode, sbj.tng_p06__description, sbj.tng_p06__description_internal, sbj.tng_p06__expectedChildbirth, sbj.tng_p06__childbirth, sbj.tng_p06__location, sbj.tng_p06__hospitalId, sbj.tng_p06__childId, sbj.tng_p06__childCode, sbj.tng_p07__description, sbj.tng_p07__description_internal, sbj.tng_p07__expectedChildbirth, sbj.tng_p07__childbirth, sbj.tng_p07__location, sbj.tng_p07__hospitalId, sbj.tng_p07__childId, sbj.tng_p07__childCode, sbj.tng_p09__description, sbj.tng_p09__description_internal, sbj.tng_p09__expectedChildbirth, sbj.tng_p09__childbirth, sbj.tng_p09__location, sbj.tng_p09__hospitalId, sbj.tng_p09__childId, sbj.tng_p09__childCode, sbj.ya_p01__description, sbj.ya_p01__description_internal, sbj.ya_p01__examin, sbj.ya_p01__familyElspac, sbj.ya_p02__description, sbj.ya_p02__description_internal, sbj.ya_p02__examin, sbj.ya_p02__familyElspac, sbj.tng_p10__description, sbj.tng_p10__description_internal, sbj.tng_p10__expectedChildbirth, sbj.tng_p10__childbirth, sbj.tng_p10__location, sbj.tng_p10__hospitalId, sbj.tng_p10__childId, sbj.tng_p10__childCode, sbj.hba_p01__description, sbj.hba_p01__description_internal, sbj.hba_p01__examin, sbj.hba_p01__familyElspac, sbj.covid_p01__description, sbj.covid_p01__description_internal, sbj.covid_p01__examin, sbj.covid_p01__familyElspac, sbj.tng_p11__description, sbj.tng_p11__description_internal, sbj.tng_p11__expectedChildbirth, sbj.tng_p11__childbirth, sbj.tng_p11__location, sbj.tng_p11__hospitalId, sbj.tng_p11__childId, sbj.tng_p11__childCode, sbj.ya_p03__description, sbj.ya_p03__description_internal, sbj.ya_p03__examin, sbj.ya_p03__familyElspac, sbj.tng_p12__description, sbj.tng_p12__description_internal, sbj.tng_p12__expectedChildbirth, sbj.tng_p12__childbirth, sbj.tng_p12__location, sbj.tng_p12__hospitalId, sbj.tng_p12__childId, sbj.tng_p12__childCode, sbj.celspac_pr_elspac_p01__description, sbj.celspac_pr_elspac_p01__description_internal, sbj.celspac_pr_elspac_p01__examin, sbj.celspac_pr_elspac_p01__familyElspac, sbj.celspac_pr_zajemci_p01__description, sbj.celspac_pr_zajemci_p01__description_internal, sbj.celspac_pr_zajemci_p01__examin, sbj.celspac_pr_zajemci_p01__familyElspac, sbj.hapiee_p01__description, sbj.hapiee_p01__description_internal, sbj.hapiee_p01__examin, sbj.hapiee_p01__familyElspac, sbj.tng_p13__description, sbj.tng_p13__description_internal, sbj.tng_p13__expectedChildbirth, sbj.tng_p13__childbirth, sbj.tng_p13__location, sbj.tng_p13__hospitalId, sbj.tng_p13__childId, sbj.tng_p13__childCode, sbj.hapiee_p02__description, sbj.hapiee_p02__description_internal, sbj.hapiee_p02__examin, sbj.hapiee_p02__familyElspac, sbj.vulde__description, sbj.vulde__description_internal, sbj.vulde__examin, sbj.vulde__familyElspac, sbj.tng_4mi_p1__description, sbj.tng_4mi_p1__description_internal, sbj.tng_4mi_p1__expectedChildbirth, sbj.tng_4mi_p1__childbirth, sbj.tng_4mi_p1__location, sbj.tng_4mi_p1__hospitalId, sbj.tng_4mi_p1__childId, sbj.tng_4mi_p1__childCode, sbj.tng_4mi_p1__examin, sbj.parents_p01__description, sbj.parents_p01__description_internal, sbj.parents_p01__examin, sbj.parents_p01__familyElspac, sbj.ya_p04__description, sbj.ya_p04__description_internal, sbj.ya_p04__examin, sbj.ya_p04__familyElspac, sbj.tng_4mi_p2__description, sbj.tng_4mi_p2__description_internal, sbj.tng_4mi_p2__expectedChildbirth, sbj.tng_4mi_p2__childbirth, sbj.tng_4mi_p2__location, sbj.tng_4mi_p2__hospitalId, sbj.tng_4mi_p2__childId, sbj.tng_4mi_p2__childCode, sbj.tng_4mi_p2__examin, sbj.tng_children_p1__description, sbj.tng_children_p1__description_internal, sbj.tng_children_p1__location, sbj.tng_children_p1__examin, sbj.tng_children_p1__childCode, sbj.parents_p02__description, sbj.parents_p02__description_internal, sbj.parents_p02__examin, sbj.parents_p02__familyElspac, sbj.horpark__description, sbj.horpark__description_internal, sbj.horpark__patientGroup, sbj.horpark__patientPartner, sbj.horpark__biobankCode, sbj.horpark__locationBloodUrine, sbj.horpark__locationLikvor, psc_role.covid_p01__role, psc_role.covid_p02__role, psc_role.covid_p03__role, psc_state.covid_p01__state, psc_state.covid_p02__state, psc_state.covid_p03__state, psc_activity_state.covid_p01__activity_state, psc_activity_state.covid_p02__activity_state, psc_activity_state.covid_p03__activity_state, psc_created_at.covid_p01__created_at, psc_created_at.covid_p02__created_at, psc_created_at.covid_p03__created_at, psc_accepted_at.covid_p01__accepted_at, psc_accepted_at.covid_p02__accepted_at, psc_accepted_at.covid_p03__accepted_at, psc_cancelled_at.covid_p01__cancelled_at, psc_cancelled_at.covid_p02__cancelled_at, psc_cancelled_at.covid_p03__cancelled_at, psc_revoked_at.covid_p01__revoked_at, psc_revoked_at.covid_p02__revoked_at, psc_revoked_at.covid_p03__revoked_at, psc_paused_at.covid_p01__paused_at, psc_paused_at.covid_p02__paused_at, psc_paused_at.covid_p03__paused_at, covid_p01_ic_venous_blood_proband, covid_p02_ic_venous_blood_proband, covid_p03_ic_venous_blood_proband FROM "celspac"."dbt_stg"."stg_celspac_admin__subject" sbj JOIN "celspac"."dbt_stg"."stg_celspac_admin__subject_inform_consent" sic ON sic.subject_about_id = sbj.id JOIN "celspac"."dbt_stg"."stg_celspac_admin__subcohort_inform_consent" scic ON scic.id = sic.subcohort_inform_consent_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__subcohort" sc ON sc.id = scic.subcohort_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__pilot" p ON p.id = sc.pilot_id AND sc.id = p.default_subcohort_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__inform_consent" ic on ic.id = scic.info_consent_id -- data JOIN proband_subcohort_role psc_role ON psc_role.subject_id = sbj.id JOIN proband_subcohort_state psc_state ON psc_state.subject_id = sbj.id JOIN proband_subcohort_activity_state psc_activity_state ON psc_activity_state.subject_id = sbj.id JOIN proband_subcohort_created_at psc_created_at ON psc_created_at.subject_id = sbj.id JOIN proband_subcohort_accepted_at psc_accepted_at ON psc_accepted_at.subject_id = sbj.id JOIN proband_subcohort_cancelled_at psc_cancelled_at ON psc_cancelled_at.subject_id = sbj.id JOIN proband_subcohort_revoked_at psc_revoked_at ON psc_revoked_at.subject_id = sbj.id JOIN proband_subcohort_paused_at psc_paused_at ON psc_paused_at.subject_id = sbj.id -- ic type BIOMATERIAL LEFT JOIN proband_ic_type_biomaterial_state ict_bio_state ON ict_bio_state.subject_id = sbj.id WHERE sbj.subject_state_code <> 'BLANK' AND ic.inform_consent_type_code = 'SUBCOHORT' AND p.id = 19 -- Pilot ID );

Date: 2025-12-09 17:11:49
Duration: 9m24s
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.export_proband_by_pilot__19"} */ create table "celspac"."dbt_marts_dbt"."export_proband_by_pilot__19__dbt_tmp" as ( -- suject (proband) patrici do kohorty COVID-19: Imunita, Pilot ID: 19 -- WITH proband_subcohort_role AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, subject_role_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS role ("subject_id" int, "covid_p01__role" text, "covid_p02__role" text, "covid_p03__role" text)), proband_subcohort_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, subject_inform_consent_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS role ("subject_id" int, "covid_p01__state" text, "covid_p02__state" text, "covid_p03__state" text)), proband_subcohort_activity_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, activity_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS activity ("subject_id" int, "covid_p01__activity_state" text, "covid_p02__activity_state" text, "covid_p03__activity_state" text)), proband_subcohort_created_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, created_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS created_at ("subject_id" int, "covid_p01__created_at" text, "covid_p02__created_at" text, "covid_p03__created_at" text)), proband_subcohort_accepted_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, accepted_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS accepted_at ("subject_id" int, "covid_p01__accepted_at" text, "covid_p02__accepted_at" text, "covid_p03__accepted_at" text)), proband_subcohort_cancelled_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, cancelled_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS cancelled_at ("subject_id" int, "covid_p01__cancelled_at" text, "covid_p02__cancelled_at" text, "covid_p03__cancelled_at" text)), proband_subcohort_revoked_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, revoked_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS revoked_at ("subject_id" int, "covid_p01__revoked_at" text, "covid_p02__revoked_at" text, "covid_p03__revoked_at" text)), proband_subcohort_paused_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, paused_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS paused_at ("subject_id" int, "covid_p01__paused_at" text, "covid_p02__paused_at" text, "covid_p03__paused_at" text)), proband_ic_type_biomaterial_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, label, subject_inform_consent_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__ict_biomaterial_base_data" WHERE pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01_ic_venous_blood_proband'), ('covid_p02_ic_venous_blood_proband'), ('covid_p03_ic_venous_blood_proband') $$ ) AS biomaterial_state ("subject_id" int, "covid_p01_ic_venous_blood_proband" text, "covid_p02_ic_venous_blood_proband" text, "covid_p03_ic_venous_blood_proband" text) ) SELECT sbj.tng_id AS proband_id, sbj.test_proband AS is_test, sbj.family_id, sbj.family_code, sbj.elspac_family_relationship_type_code, sbj.subject_state_code, sbj.rc, sbj.sex_code, sbj.birthdate, sbj.death_at, sbj.date_of_death, sbj.cause_of_death, sbj.titles_before, sbj.name, sbj.surname, sbj.titles_after, sbj.email, sbj.phone_number, sbj.street, sbj.street_number, sbj.city, sbj.postal_code, sbj.country, sbj.street_contact, sbj.street_number_contact, sbj.city_contact, sbj.postal_code_contact, sbj.country_contact, sbj.is_origin_elspac, sbj.description, sbj.tng_p08__description, sbj.tng_p08__description_internal, sbj.tng_p08__expectedChildbirth, sbj.tng_p08__childbirth, sbj.tng_p08__location, sbj.tng_p08__hospitalId, sbj.tng_p08__childId, sbj.tng_p08__childCode, sbj.tng_p01__description, sbj.tng_p01__description_internal, sbj.tng_p01__expectedChildbirth, sbj.tng_p01__childbirth, sbj.tng_p01__location, sbj.tng_p01__hospitalId, sbj.tng_p01__childId, sbj.tng_p01__childCode, sbj.tng_p02__description, sbj.tng_p02__description_internal, sbj.tng_p02__expectedChildbirth, sbj.tng_p02__childbirth, sbj.tng_p02__location, sbj.tng_p02__hospitalId, sbj.tng_p02__childId, sbj.tng_p02__childCode, sbj.tng_p03__description, sbj.tng_p03__description_internal, sbj.tng_p03__expectedChildbirth, sbj.tng_p03__childbirth, sbj.tng_p03__location, sbj.tng_p03__hospitalId, sbj.tng_p03__childId, sbj.tng_p03__childCode, sbj.tng_p04__description, sbj.tng_p04__description_internal, sbj.tng_p04__expectedChildbirth, sbj.tng_p04__childbirth, sbj.tng_p04__location, sbj.tng_p04__hospitalId, sbj.tng_p04__childId, sbj.tng_p04__childCode, sbj.tng_p05__description, sbj.tng_p05__description_internal, sbj.tng_p05__expectedChildbirth, sbj.tng_p05__childbirth, sbj.tng_p05__location, sbj.tng_p05__hospitalId, sbj.tng_p05__childId, sbj.tng_p05__childCode, sbj.tng_p06__description, sbj.tng_p06__description_internal, sbj.tng_p06__expectedChildbirth, sbj.tng_p06__childbirth, sbj.tng_p06__location, sbj.tng_p06__hospitalId, sbj.tng_p06__childId, sbj.tng_p06__childCode, sbj.tng_p07__description, sbj.tng_p07__description_internal, sbj.tng_p07__expectedChildbirth, sbj.tng_p07__childbirth, sbj.tng_p07__location, sbj.tng_p07__hospitalId, sbj.tng_p07__childId, sbj.tng_p07__childCode, sbj.tng_p09__description, sbj.tng_p09__description_internal, sbj.tng_p09__expectedChildbirth, sbj.tng_p09__childbirth, sbj.tng_p09__location, sbj.tng_p09__hospitalId, sbj.tng_p09__childId, sbj.tng_p09__childCode, sbj.ya_p01__description, sbj.ya_p01__description_internal, sbj.ya_p01__examin, sbj.ya_p01__familyElspac, sbj.ya_p02__description, sbj.ya_p02__description_internal, sbj.ya_p02__examin, sbj.ya_p02__familyElspac, sbj.tng_p10__description, sbj.tng_p10__description_internal, sbj.tng_p10__expectedChildbirth, sbj.tng_p10__childbirth, sbj.tng_p10__location, sbj.tng_p10__hospitalId, sbj.tng_p10__childId, sbj.tng_p10__childCode, sbj.hba_p01__description, sbj.hba_p01__description_internal, sbj.hba_p01__examin, sbj.hba_p01__familyElspac, sbj.covid_p01__description, sbj.covid_p01__description_internal, sbj.covid_p01__examin, sbj.covid_p01__familyElspac, sbj.tng_p11__description, sbj.tng_p11__description_internal, sbj.tng_p11__expectedChildbirth, sbj.tng_p11__childbirth, sbj.tng_p11__location, sbj.tng_p11__hospitalId, sbj.tng_p11__childId, sbj.tng_p11__childCode, sbj.ya_p03__description, sbj.ya_p03__description_internal, sbj.ya_p03__examin, sbj.ya_p03__familyElspac, sbj.tng_p12__description, sbj.tng_p12__description_internal, sbj.tng_p12__expectedChildbirth, sbj.tng_p12__childbirth, sbj.tng_p12__location, sbj.tng_p12__hospitalId, sbj.tng_p12__childId, sbj.tng_p12__childCode, sbj.celspac_pr_elspac_p01__description, sbj.celspac_pr_elspac_p01__description_internal, sbj.celspac_pr_elspac_p01__examin, sbj.celspac_pr_elspac_p01__familyElspac, sbj.celspac_pr_zajemci_p01__description, sbj.celspac_pr_zajemci_p01__description_internal, sbj.celspac_pr_zajemci_p01__examin, sbj.celspac_pr_zajemci_p01__familyElspac, sbj.hapiee_p01__description, sbj.hapiee_p01__description_internal, sbj.hapiee_p01__examin, sbj.hapiee_p01__familyElspac, sbj.tng_p13__description, sbj.tng_p13__description_internal, sbj.tng_p13__expectedChildbirth, sbj.tng_p13__childbirth, sbj.tng_p13__location, sbj.tng_p13__hospitalId, sbj.tng_p13__childId, sbj.tng_p13__childCode, sbj.hapiee_p02__description, sbj.hapiee_p02__description_internal, sbj.hapiee_p02__examin, sbj.hapiee_p02__familyElspac, sbj.vulde__description, sbj.vulde__description_internal, sbj.vulde__examin, sbj.vulde__familyElspac, sbj.tng_4mi_p1__description, sbj.tng_4mi_p1__description_internal, sbj.tng_4mi_p1__expectedChildbirth, sbj.tng_4mi_p1__childbirth, sbj.tng_4mi_p1__location, sbj.tng_4mi_p1__hospitalId, sbj.tng_4mi_p1__childId, sbj.tng_4mi_p1__childCode, sbj.tng_4mi_p1__examin, sbj.parents_p01__description, sbj.parents_p01__description_internal, sbj.parents_p01__examin, sbj.parents_p01__familyElspac, sbj.ya_p04__description, sbj.ya_p04__description_internal, sbj.ya_p04__examin, sbj.ya_p04__familyElspac, sbj.tng_4mi_p2__description, sbj.tng_4mi_p2__description_internal, sbj.tng_4mi_p2__expectedChildbirth, sbj.tng_4mi_p2__childbirth, sbj.tng_4mi_p2__location, sbj.tng_4mi_p2__hospitalId, sbj.tng_4mi_p2__childId, sbj.tng_4mi_p2__childCode, sbj.tng_4mi_p2__examin, sbj.tng_children_p1__description, sbj.tng_children_p1__description_internal, sbj.tng_children_p1__location, sbj.tng_children_p1__examin, sbj.tng_children_p1__childCode, sbj.parents_p02__description, sbj.parents_p02__description_internal, sbj.parents_p02__examin, sbj.parents_p02__familyElspac, sbj.horpark__description, sbj.horpark__description_internal, sbj.horpark__patientGroup, sbj.horpark__patientPartner, sbj.horpark__biobankCode, sbj.horpark__locationBloodUrine, sbj.horpark__locationLikvor, psc_role.covid_p01__role, psc_role.covid_p02__role, psc_role.covid_p03__role, psc_state.covid_p01__state, psc_state.covid_p02__state, psc_state.covid_p03__state, psc_activity_state.covid_p01__activity_state, psc_activity_state.covid_p02__activity_state, psc_activity_state.covid_p03__activity_state, psc_created_at.covid_p01__created_at, psc_created_at.covid_p02__created_at, psc_created_at.covid_p03__created_at, psc_accepted_at.covid_p01__accepted_at, psc_accepted_at.covid_p02__accepted_at, psc_accepted_at.covid_p03__accepted_at, psc_cancelled_at.covid_p01__cancelled_at, psc_cancelled_at.covid_p02__cancelled_at, psc_cancelled_at.covid_p03__cancelled_at, psc_revoked_at.covid_p01__revoked_at, psc_revoked_at.covid_p02__revoked_at, psc_revoked_at.covid_p03__revoked_at, psc_paused_at.covid_p01__paused_at, psc_paused_at.covid_p02__paused_at, psc_paused_at.covid_p03__paused_at, covid_p01_ic_venous_blood_proband, covid_p02_ic_venous_blood_proband, covid_p03_ic_venous_blood_proband FROM "celspac"."dbt_stg"."stg_celspac_admin__subject" sbj JOIN "celspac"."dbt_stg"."stg_celspac_admin__subject_inform_consent" sic ON sic.subject_about_id = sbj.id JOIN "celspac"."dbt_stg"."stg_celspac_admin__subcohort_inform_consent" scic ON scic.id = sic.subcohort_inform_consent_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__subcohort" sc ON sc.id = scic.subcohort_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__pilot" p ON p.id = sc.pilot_id AND sc.id = p.default_subcohort_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__inform_consent" ic on ic.id = scic.info_consent_id -- data JOIN proband_subcohort_role psc_role ON psc_role.subject_id = sbj.id JOIN proband_subcohort_state psc_state ON psc_state.subject_id = sbj.id JOIN proband_subcohort_activity_state psc_activity_state ON psc_activity_state.subject_id = sbj.id JOIN proband_subcohort_created_at psc_created_at ON psc_created_at.subject_id = sbj.id JOIN proband_subcohort_accepted_at psc_accepted_at ON psc_accepted_at.subject_id = sbj.id JOIN proband_subcohort_cancelled_at psc_cancelled_at ON psc_cancelled_at.subject_id = sbj.id JOIN proband_subcohort_revoked_at psc_revoked_at ON psc_revoked_at.subject_id = sbj.id JOIN proband_subcohort_paused_at psc_paused_at ON psc_paused_at.subject_id = sbj.id -- ic type BIOMATERIAL LEFT JOIN proband_ic_type_biomaterial_state ict_bio_state ON ict_bio_state.subject_id = sbj.id WHERE sbj.subject_state_code <> 'BLANK' AND ic.inform_consent_type_code = 'SUBCOHORT' AND p.id = 19 -- Pilot ID );

Date: 2025-12-09 05:08:36
Duration: 6m13s
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt
Log file: ssh://root@ucet.celspac.cz//var/log/postgresql/postgresql-16-main.log

/* {"app": "dbt", "dbt_version": "1.9.1", "profile_name": "celspacadmindbt", "target_name": "main", "node_id": "model.celspacadmindbt.export_proband_by_pilot__19"} */ create table "celspac"."dbt_marts_dbt"."export_proband_by_pilot__19__dbt_tmp" as ( -- suject (proband) patrici do kohorty COVID-19: Imunita, Pilot ID: 19 -- WITH proband_subcohort_role AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, subject_role_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS role ("subject_id" int, "covid_p01__role" text, "covid_p02__role" text, "covid_p03__role" text)), proband_subcohort_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, subject_inform_consent_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS role ("subject_id" int, "covid_p01__state" text, "covid_p02__state" text, "covid_p03__state" text)), proband_subcohort_activity_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, activity_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS activity ("subject_id" int, "covid_p01__activity_state" text, "covid_p02__activity_state" text, "covid_p03__activity_state" text)), proband_subcohort_created_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, created_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS created_at ("subject_id" int, "covid_p01__created_at" text, "covid_p02__created_at" text, "covid_p03__created_at" text)), proband_subcohort_accepted_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, accepted_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS accepted_at ("subject_id" int, "covid_p01__accepted_at" text, "covid_p02__accepted_at" text, "covid_p03__accepted_at" text)), proband_subcohort_cancelled_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, cancelled_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS cancelled_at ("subject_id" int, "covid_p01__cancelled_at" text, "covid_p02__cancelled_at" text, "covid_p03__cancelled_at" text)), proband_subcohort_revoked_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, revoked_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS revoked_at ("subject_id" int, "covid_p01__revoked_at" text, "covid_p02__revoked_at" text, "covid_p03__revoked_at" text)), proband_subcohort_paused_at AS ( SELECT * FROM crosstab ($$ SELECT subject_id, subcohort_label, paused_at FROM "celspac"."dbt_marts_dbt_all"."intr__proband_base_data" WHERE subject_state_code <> 'BLANK' AND inform_consent_type_code = 'SUBCOHORT' AND pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01'), ('covid_p02'), ('covid_p03') $$ ) AS paused_at ("subject_id" int, "covid_p01__paused_at" text, "covid_p02__paused_at" text, "covid_p03__paused_at" text)), proband_ic_type_biomaterial_state AS ( SELECT * FROM crosstab ($$ SELECT subject_id, label, subject_inform_consent_state_code FROM "celspac"."dbt_marts_dbt_all"."intr__ict_biomaterial_base_data" WHERE pilot_id = 19 -- Pilot ID ORDER BY 1, 2 $$, $$ VALUES ('covid_p01_ic_venous_blood_proband'), ('covid_p02_ic_venous_blood_proband'), ('covid_p03_ic_venous_blood_proband') $$ ) AS biomaterial_state ("subject_id" int, "covid_p01_ic_venous_blood_proband" text, "covid_p02_ic_venous_blood_proband" text, "covid_p03_ic_venous_blood_proband" text) ) SELECT sbj.tng_id AS proband_id, sbj.test_proband AS is_test, sbj.family_id, sbj.family_code, sbj.elspac_family_relationship_type_code, sbj.subject_state_code, sbj.rc, sbj.sex_code, sbj.birthdate, sbj.death_at, sbj.date_of_death, sbj.cause_of_death, sbj.titles_before, sbj.name, sbj.surname, sbj.titles_after, sbj.email, sbj.phone_number, sbj.street, sbj.street_number, sbj.city, sbj.postal_code, sbj.country, sbj.street_contact, sbj.street_number_contact, sbj.city_contact, sbj.postal_code_contact, sbj.country_contact, sbj.is_origin_elspac, sbj.description, sbj.tng_p08__description, sbj.tng_p08__description_internal, sbj.tng_p08__expectedChildbirth, sbj.tng_p08__childbirth, sbj.tng_p08__location, sbj.tng_p08__hospitalId, sbj.tng_p08__childId, sbj.tng_p08__childCode, sbj.tng_p01__description, sbj.tng_p01__description_internal, sbj.tng_p01__expectedChildbirth, sbj.tng_p01__childbirth, sbj.tng_p01__location, sbj.tng_p01__hospitalId, sbj.tng_p01__childId, sbj.tng_p01__childCode, sbj.tng_p02__description, sbj.tng_p02__description_internal, sbj.tng_p02__expectedChildbirth, sbj.tng_p02__childbirth, sbj.tng_p02__location, sbj.tng_p02__hospitalId, sbj.tng_p02__childId, sbj.tng_p02__childCode, sbj.tng_p03__description, sbj.tng_p03__description_internal, sbj.tng_p03__expectedChildbirth, sbj.tng_p03__childbirth, sbj.tng_p03__location, sbj.tng_p03__hospitalId, sbj.tng_p03__childId, sbj.tng_p03__childCode, sbj.tng_p04__description, sbj.tng_p04__description_internal, sbj.tng_p04__expectedChildbirth, sbj.tng_p04__childbirth, sbj.tng_p04__location, sbj.tng_p04__hospitalId, sbj.tng_p04__childId, sbj.tng_p04__childCode, sbj.tng_p05__description, sbj.tng_p05__description_internal, sbj.tng_p05__expectedChildbirth, sbj.tng_p05__childbirth, sbj.tng_p05__location, sbj.tng_p05__hospitalId, sbj.tng_p05__childId, sbj.tng_p05__childCode, sbj.tng_p06__description, sbj.tng_p06__description_internal, sbj.tng_p06__expectedChildbirth, sbj.tng_p06__childbirth, sbj.tng_p06__location, sbj.tng_p06__hospitalId, sbj.tng_p06__childId, sbj.tng_p06__childCode, sbj.tng_p07__description, sbj.tng_p07__description_internal, sbj.tng_p07__expectedChildbirth, sbj.tng_p07__childbirth, sbj.tng_p07__location, sbj.tng_p07__hospitalId, sbj.tng_p07__childId, sbj.tng_p07__childCode, sbj.tng_p09__description, sbj.tng_p09__description_internal, sbj.tng_p09__expectedChildbirth, sbj.tng_p09__childbirth, sbj.tng_p09__location, sbj.tng_p09__hospitalId, sbj.tng_p09__childId, sbj.tng_p09__childCode, sbj.ya_p01__description, sbj.ya_p01__description_internal, sbj.ya_p01__examin, sbj.ya_p01__familyElspac, sbj.ya_p02__description, sbj.ya_p02__description_internal, sbj.ya_p02__examin, sbj.ya_p02__familyElspac, sbj.tng_p10__description, sbj.tng_p10__description_internal, sbj.tng_p10__expectedChildbirth, sbj.tng_p10__childbirth, sbj.tng_p10__location, sbj.tng_p10__hospitalId, sbj.tng_p10__childId, sbj.tng_p10__childCode, sbj.hba_p01__description, sbj.hba_p01__description_internal, sbj.hba_p01__examin, sbj.hba_p01__familyElspac, sbj.covid_p01__description, sbj.covid_p01__description_internal, sbj.covid_p01__examin, sbj.covid_p01__familyElspac, sbj.tng_p11__description, sbj.tng_p11__description_internal, sbj.tng_p11__expectedChildbirth, sbj.tng_p11__childbirth, sbj.tng_p11__location, sbj.tng_p11__hospitalId, sbj.tng_p11__childId, sbj.tng_p11__childCode, sbj.ya_p03__description, sbj.ya_p03__description_internal, sbj.ya_p03__examin, sbj.ya_p03__familyElspac, sbj.tng_p12__description, sbj.tng_p12__description_internal, sbj.tng_p12__expectedChildbirth, sbj.tng_p12__childbirth, sbj.tng_p12__location, sbj.tng_p12__hospitalId, sbj.tng_p12__childId, sbj.tng_p12__childCode, sbj.celspac_pr_elspac_p01__description, sbj.celspac_pr_elspac_p01__description_internal, sbj.celspac_pr_elspac_p01__examin, sbj.celspac_pr_elspac_p01__familyElspac, sbj.celspac_pr_zajemci_p01__description, sbj.celspac_pr_zajemci_p01__description_internal, sbj.celspac_pr_zajemci_p01__examin, sbj.celspac_pr_zajemci_p01__familyElspac, sbj.hapiee_p01__description, sbj.hapiee_p01__description_internal, sbj.hapiee_p01__examin, sbj.hapiee_p01__familyElspac, sbj.tng_p13__description, sbj.tng_p13__description_internal, sbj.tng_p13__expectedChildbirth, sbj.tng_p13__childbirth, sbj.tng_p13__location, sbj.tng_p13__hospitalId, sbj.tng_p13__childId, sbj.tng_p13__childCode, sbj.hapiee_p02__description, sbj.hapiee_p02__description_internal, sbj.hapiee_p02__examin, sbj.hapiee_p02__familyElspac, sbj.vulde__description, sbj.vulde__description_internal, sbj.vulde__examin, sbj.vulde__familyElspac, sbj.tng_4mi_p1__description, sbj.tng_4mi_p1__description_internal, sbj.tng_4mi_p1__expectedChildbirth, sbj.tng_4mi_p1__childbirth, sbj.tng_4mi_p1__location, sbj.tng_4mi_p1__hospitalId, sbj.tng_4mi_p1__childId, sbj.tng_4mi_p1__childCode, sbj.tng_4mi_p1__examin, sbj.parents_p01__description, sbj.parents_p01__description_internal, sbj.parents_p01__examin, sbj.parents_p01__familyElspac, sbj.ya_p04__description, sbj.ya_p04__description_internal, sbj.ya_p04__examin, sbj.ya_p04__familyElspac, sbj.tng_4mi_p2__description, sbj.tng_4mi_p2__description_internal, sbj.tng_4mi_p2__expectedChildbirth, sbj.tng_4mi_p2__childbirth, sbj.tng_4mi_p2__location, sbj.tng_4mi_p2__hospitalId, sbj.tng_4mi_p2__childId, sbj.tng_4mi_p2__childCode, sbj.tng_4mi_p2__examin, sbj.tng_children_p1__description, sbj.tng_children_p1__description_internal, sbj.tng_children_p1__location, sbj.tng_children_p1__examin, sbj.tng_children_p1__childCode, sbj.parents_p02__description, sbj.parents_p02__description_internal, sbj.parents_p02__examin, sbj.parents_p02__familyElspac, sbj.horpark__description, sbj.horpark__description_internal, sbj.horpark__patientGroup, sbj.horpark__patientPartner, sbj.horpark__biobankCode, sbj.horpark__locationBloodUrine, sbj.horpark__locationLikvor, psc_role.covid_p01__role, psc_role.covid_p02__role, psc_role.covid_p03__role, psc_state.covid_p01__state, psc_state.covid_p02__state, psc_state.covid_p03__state, psc_activity_state.covid_p01__activity_state, psc_activity_state.covid_p02__activity_state, psc_activity_state.covid_p03__activity_state, psc_created_at.covid_p01__created_at, psc_created_at.covid_p02__created_at, psc_created_at.covid_p03__created_at, psc_accepted_at.covid_p01__accepted_at, psc_accepted_at.covid_p02__accepted_at, psc_accepted_at.covid_p03__accepted_at, psc_cancelled_at.covid_p01__cancelled_at, psc_cancelled_at.covid_p02__cancelled_at, psc_cancelled_at.covid_p03__cancelled_at, psc_revoked_at.covid_p01__revoked_at, psc_revoked_at.covid_p02__revoked_at, psc_revoked_at.covid_p03__revoked_at, psc_paused_at.covid_p01__paused_at, psc_paused_at.covid_p02__paused_at, psc_paused_at.covid_p03__paused_at, covid_p01_ic_venous_blood_proband, covid_p02_ic_venous_blood_proband, covid_p03_ic_venous_blood_proband FROM "celspac"."dbt_stg"."stg_celspac_admin__subject" sbj JOIN "celspac"."dbt_stg"."stg_celspac_admin__subject_inform_consent" sic ON sic.subject_about_id = sbj.id JOIN "celspac"."dbt_stg"."stg_celspac_admin__subcohort_inform_consent" scic ON scic.id = sic.subcohort_inform_consent_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__subcohort" sc ON sc.id = scic.subcohort_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__pilot" p ON p.id = sc.pilot_id AND sc.id = p.default_subcohort_id JOIN "celspac"."dbt_stg"."stg_celspac_admin__inform_consent" ic on ic.id = scic.info_consent_id -- data JOIN proband_subcohort_role psc_role ON psc_role.subject_id = sbj.id JOIN proband_subcohort_state psc_state ON psc_state.subject_id = sbj.id JOIN proband_subcohort_activity_state psc_activity_state ON psc_activity_state.subject_id = sbj.id JOIN proband_subcohort_created_at psc_created_at ON psc_created_at.subject_id = sbj.id JOIN proband_subcohort_accepted_at psc_accepted_at ON psc_accepted_at.subject_id = sbj.id JOIN proband_subcohort_cancelled_at psc_cancelled_at ON psc_cancelled_at.subject_id = sbj.id JOIN proband_subcohort_revoked_at psc_revoked_at ON psc_revoked_at.subject_id = sbj.id JOIN proband_subcohort_paused_at psc_paused_at ON psc_paused_at.subject_id = sbj.id -- ic type BIOMATERIAL LEFT JOIN proband_ic_type_biomaterial_state ict_bio_state ON ict_bio_state.subject_id = sbj.id WHERE sbj.subject_state_code <> 'BLANK' AND ic.inform_consent_type_code = 'SUBCOHORT' AND p.id = 19 -- Pilot ID );

Date: 2025-12-09 05:02:24
Duration: 0ms
Database: celspac
User: tng_migration_user
Remote: 127.0.0.1
Application: dbt

x Hide