create_tables_ERR.sql
application/octet-stream
Filename: create_tables_ERR.sql
Type: application/octet-stream
Part: 0
--------------------------------------------------------------------------------------- create table admin.gaia_source_urls_imp ( iline int generated by default as identity ,cname text ,cdate text ,isize_compr int ) ; --------------------------------------------------------------------------------------- create table admin.gaia_hb_md5_imp ( cline text ) ; create table admin.gaia_hb_lines_imp ( cline text ) ; --------------------------------------------------------------------------------------- create table admin.gaia_urls as with qhp as ( select iline as gurl_id_gsi ,ihp_start>>16 as gurl_l0_hp ,ihp_start>>16 as gurl_l0_hp_start ,ihp_end>>16 as gurl_l0_hp_end ,(ihp_start>>4) as gurl_ibhp_start ,(ihp_end>>4) as gurl_ibhp_end ,(ihp_start>>4)&((1<<12)-1) as gurl_bigsubp_start ,(ihp_end>>4)&((1<<12)-1) as gurl_bigsubp_end ,lead(ihp_start>>4) over (order by iline) as gurl_ibhp_next_start ,isize_compr as gurl_nbytes_compr ,NULL::int as gurl_nbytes_uc ,NULL::int as gurl_nbytes_est_uc ,NULL::int as gurl_nlines_count ,NULL::int as gurl_nlines_est ,cre_date as gurl_uc_date ,cname::varchar(120) as gurl_uc_fname ,NULL::varchar(120) as gurl_uc_full_file_path ,NULL::varchar(32) as gurl_md5_orig ,NULL::varchar(132) as gurl_md5_calc from ( select iline ,cname ,substr(cname,12,6)::int as ihp_start ,substr(cname,19,6)::int as ihp_end ,NULL::int as isize_compr ,to_date(cdate,'DD-MON-YYYY') as cre_date from gaia_source_urls_imp -- limit 400 ) qinp ) select * from qhp; alter table gaia_urls add constraint gurl_pkey primary key (gurl_id_gsi); -- cluster gaia_urls using uk_id_gs_start; create index if not exists brin_l0_hp on gaia_urls using brin (gurl_l0_hp) with (pages_per_range=32) ; ------------------------------------------------------------------------ create materialized view admin.gurl_load_intervals as with qg as ( select row_number () over (partition by gurl_l0_hp) as igrow ,gurl_l0_hp ,gurl_id_gsi ,gurl_ibhp_start ,gurl_ibhp_end ,gurl_ibhp_start-lag(gurl_ibhp_end) over () as inext_bhp from gaia_urls ) ,qg2 as ( select * ,lag(nurl) over (partition by gurl_l0_hp order by gurl_id_gsi) as prev_nurl ,lead(nurl) over (partition by gurl_l0_hp order by gurl_id_gsi) as next_nurl from ( select * ,gurl_id_gsi-lag(gurl_id_gsi) over (order by gurl_id_gsi) as nurl from ( select gurl_l0_hp ,gurl_id_gsi ,gurl_ibhp_start ,1 as ityp from qg where inext_bhp=1 union all select gurl_l0_hp ,gurl_id_gsi ,gurl_ibhp_start ,0 as ityp from gaia_urls where gurl_l0_hp_start != gurl_l0_hp_end ) qgdelta ) qgprev ) select * ,gurl_id_gsi-lag(gurl_id_gsi) over (order by gurl_id_gsi) as nurl_read from qg2 where gurl_l0_hp >= 0 and (nurl >12 or (nurl <=12 and (prev_nurl > 50 or next_nurl > 50)) or ityp=0); --------------------------------------------------------------------------------------- -- drop table source_imp; create unlogged table source_imp ( solution_id bigint ,designation varchar(40) ,source_id bigint ,random_index bigint ,ref_epoch varchar(10) ,ra double precision ,ra_error real ,dec double precision ,dec_error real ,parallax double precision ,parallax_error real ,parallax_over_error real ,pm real ,pmra double precision ,pmra_error real ,pmdec double precision ,pmdec_error real ,ra_dec_corr real ,ra_parallax_corr real ,ra_pmra_corr real ,ra_pmdec_corr real ,dec_parallax_corr real ,dec_pmra_corr real ,dec_pmdec_corr real ,parallax_pmra_corr real ,parallax_pmdec_corr real ,pmra_pmdec_corr real ,astrometric_n_obs_al int ,astrometric_n_obs_ac int ,astrometric_n_good_obs_al int ,astrometric_n_bad_obs_al int ,astrometric_gof_al real ,astrometric_chi2_al real ,astrometric_excess_noise real ,astrometric_excess_noise_sig real ,astrometric_params_solved smallint ,astrometric_primary_flag boolean ,nu_eff_used_in_astrometry real ,pseudocolour real ,pseudocolour_error real ,ra_pseudocolour_corr real ,dec_pseudocolour_corr real ,parallax_pseudocolour_corr real ,pmra_pseudocolour_corr real ,pmdec_pseudocolour_corr real ,astrometric_matched_transits int ,visibility_periods_used int ,astrometric_sigma5d_max real ,matched_transits int ,new_matched_transits int ,matched_transits_removed int ,ipd_gof_harmonic_amplitude real ,ipd_gof_harmonic_phase real ,ipd_frac_multi_peak smallint ,ipd_frac_odd_win smallint ,ruwe real ,scan_direction_strength_k1 real ,scan_direction_strength_k2 real ,scan_direction_strength_k3 real ,scan_direction_strength_k4 real ,scan_direction_mean_k1 real ,scan_direction_mean_k2 real ,scan_direction_mean_k3 real ,scan_direction_mean_k4 real ,duplicated_source boolean ,phot_g_n_obs int ,phot_g_mean_flux double precision ,phot_g_mean_flux_error real ,phot_g_mean_flux_over_error real ,phot_g_mean_mag real ,phot_bp_n_obs int ,phot_bp_mean_flux double precision ,phot_bp_mean_flux_error real ,phot_bp_mean_flux_over_error real ,phot_bp_mean_mag real ,phot_rp_n_obs int ,phot_rp_mean_flux double precision ,phot_rp_mean_flux_error real ,phot_rp_mean_flux_over_error real ,phot_rp_mean_mag real ,phot_bp_rp_excess_factor real ,phot_bp_n_contaminated_transits int ,phot_bp_n_blended_transits int ,phot_rp_n_contaminated_transits int ,phot_rp_n_blended_transits int ,phot_proc_mode smallint ,bp_rp real ,bp_g real ,g_rp real ,radial_velocity double precision ,radial_velocity_error double precision ,rv_method_used int ,rv_nb_transits int ,rv_nb_deblended_transits int ,rv_visibility_periods_used int ,rv_expected_sig_to_noise real ,rv_renormalised_gof real ,rv_chisq_pvalue real ,rv_time_duration real ,rv_amplitude_robust real ,rv_template_teff real ,rv_template_logg real ,rv_template_fe_h real ,rv_atm_param_origin int ,vbroad real ,vbroad_error real ,vbroad_nb_transits int ,grvs_mag real ,grvs_mag_error real ,grvs_mag_nb_transits int ,rvs_spec_sig_to_noise real ,phot_variable_flag varchar(120) ,l double precision ,b double precision ,ecl_lon double precision ,ecl_lat double precision ,in_qso_candidates boolean ,in_galaxy_candidates boolean ,non_single_star int ,has_xp_continuous boolean ,has_xp_sampled boolean ,has_rvs boolean ,has_epoch_photometry boolean ,has_epoch_rv boolean ,has_mcmc_gspphot boolean ,has_mcmc_msc boolean ,in_andromeda_survey boolean ,classprob_dsc_combmod_quasar real ,classprob_dsc_combmod_galaxy real ,classprob_dsc_combmod_star real ,teff_gspphot real ,teff_gspphot_lower real ,teff_gspphot_upper real ,logg_gspphot real ,logg_gspphot_lower real ,logg_gspphot_upper real ,mh_gspphot real ,mh_gspphot_lower real ,mh_gspphot_upper real ,distance_gspphot real ,distance_gspphot_lower real ,distance_gspphot_upper real ,azero_gspphot real ,azero_gspphot_lower real ,azero_gspphot_upper real ,ag_gspphot real ,ag_gspphot_lower real ,ag_gspphot_upper real ,ebpminrp_gspphot real ,ebpminrp_gspphot_lower real ,ebpminrp_gspphot_upper real ,libname_gspphot text ); /* ,phot_bp_rp_excess_factor ,phot_bp_n_contaminated_transits ,phot_bp_n_blended_transits ,phot_rp_n_contaminated_transits ,phot_rp_n_blended_transits ,phot_proc_mode ,bp_rp ,bp_g ,g_rp ,radial_velocity ,radial_velocity_error ,rv_method_used ,rv_nb_transits ,rv_nb_deblended_transits ,rv_visibility_periods_used ,rv_expected_sig_to_noise ,rv_renormalised_gof ,rv_chisq_pvalue ,rv_time_duration ,rv_amplitude_robust ,rv_template_teff ,rv_template_logg ,rv_template_fe_h ,rv_atm_param_origin ,vbroad ,vbroad_error ,vbroad_nb_transits ,grvs_mag ,grvs_mag_error ,grvs_mag_nb_transits ,rvs_spec_sig_to_noise ,phot_variable_flag ,l ,b ,ecl_lon ,ecl_lat ,in_qso_candidates ,in_galaxy_candidates ,non_single_star ,has_xp_continuous ,has_xp_sampled ,has_rvs ,has_epoch_photometry ,has_epoch_rv ,has_mcmc_gspphot ,has_mcmc_msc ,in_andromeda_survey ,classprob_dsc_combmod_quasar ,classprob_dsc_combmod_galaxy ,classprob_dsc_combmod_star ,teff_gspphot ,teff_gspphot_lower ,teff_gspphot_upper ,logg_gspphot ,logg_gspphot_lower ,logg_gspphot_upper ,mh_gspphot ,mh_gspphot_lower ,mh_gspphot_upper ,distance_gspphot ,distance_gspphot_lower ,distance_gspphot_upper ,azero_gspphot ,azero_gspphot_lower ,azero_gspphot_upper ,ag_gspphot ,ag_gspphot_lower ,ag_gspphot_upper ,ebpminrp_gspphot ,ebpminrp_gspphot_lower ,ebpminrp_gspphot_upper ,libname_gspphot */ create schema admin; create table admin.gaia_subhp ( id_gshp int ,gshp_ra_mul smallint ,gshp_dec_mul smallint ,gshp_ra_delta int ,gshp_dec_delta int ); -------------------- Introducing partitioning create table gaia_id_arr ( id int ,l0_hp int --,ioffs_arr int[] ,isubhp_arr smallint[] ,iorg_arr smallint[] ,isource_seq_arr int[] ,ira_arr int[] ,idec_arr int[] ) partition by list (l0_hp) ; create table gaia_id_arr_0 partition of gaia_id_arr for values in (0); create table gaia_id_arr_1 partition of gaia_id_arr for values in (1); create table gaia_id_arr_2 partition of gaia_id_arr for values in (2); create table gaia_id_arr_3 partition of gaia_id_arr for values in (3); create table gaia_id_arr_4 partition of gaia_id_arr for values in (4); create table gaia_id_arr_5 partition of gaia_id_arr for values in (5); create table gaia_id_arr_6 partition of gaia_id_arr for values in (6); create table gaia_id_arr_7 partition of gaia_id_arr for values in (7); create table gaia_id_arr_8 partition of gaia_id_arr for values in (8); create table gaia_id_arr_9 partition of gaia_id_arr for values in (9); create table gaia_id_arr_10 partition of gaia_id_arr for values in (10); create table gaia_id_arr_11 partition of gaia_id_arr for values in (11); begin; insert into gaia_id_arr select ibhp ,ibhp>>12 from generate_series ((0<<12)+0,(11<<12)+4095) as ibhp ; commit; create index if not exists idx_id_arr_0 on gaia_id_arr_0 (id); create index if not exists idx_id_arr_1 on gaia_id_arr_1 (id); create index if not exists idx_id_arr_2 on gaia_id_arr_2 (id); create index if not exists idx_id_arr_3 on gaia_id_arr_3 (id); create index if not exists idx_id_arr_4 on gaia_id_arr_4 (id); create index if not exists idx_id_arr_5 on gaia_id_arr_5 (id); create index if not exists idx_id_arr_6 on gaia_id_arr_6 (id); create index if not exists idx_id_arr_7 on gaia_id_arr_7 (id); create index if not exists idx_id_arr_8 on gaia_id_arr_8 (id); create index if not exists idx_id_arr_9 on gaia_id_arr_9 (id); create index if not exists idx_id_arr_10 on gaia_id_arr_10 (id); create index if not exists idx_id_arr_11 on gaia_id_arr_11 (id); create table gaia_ovfl_arr ( id int ,igrp int ,l0_hp int ,target_offs int ,ntarget int ,min_seq int ,max_seq int ,min_gdelta int ,max_gdelta int ,gdelta_arr int[] ,source_id_arr bigint[] ) partition by list (l0_hp) ; create table gaia_ovfl_arr_0 partition of gaia_ovfl_arr for values in (0); create table gaia_ovfl_arr_1 partition of gaia_ovfl_arr for values in (1); create table gaia_ovfl_arr_2 partition of gaia_ovfl_arr for values in (2); create table gaia_ovfl_arr_3 partition of gaia_ovfl_arr for values in (3); create table gaia_ovfl_arr_4 partition of gaia_ovfl_arr for values in (4); create table gaia_ovfl_arr_5 partition of gaia_ovfl_arr for values in (5); create table gaia_ovfl_arr_6 partition of gaia_ovfl_arr for values in (6); create table gaia_ovfl_arr_7 partition of gaia_ovfl_arr for values in (7); create table gaia_ovfl_arr_8 partition of gaia_ovfl_arr for values in (8); create table gaia_ovfl_arr_9 partition of gaia_ovfl_arr for values in (9); create table gaia_ovfl_arr_10 partition of gaia_ovfl_arr for values in (10); create table gaia_ovfl_arr_11 partition of gaia_ovfl_arr for values in (11); create unique index if not exists uk_ovfl_arr_0 on gaia_ovfl_arr_0 (id,igrp); create unique index if not exists uk_ovfl_arr_1 on gaia_ovfl_arr_1 (id,igrp); create unique index if not exists uk_ovfl_arr_2 on gaia_ovfl_arr_2 (id,igrp); create unique index if not exists uk_ovfl_arr_3 on gaia_ovfl_arr_3 (id,igrp); create unique index if not exists uk_ovfl_arr_4 on gaia_ovfl_arr_4 (id,igrp); create unique index if not exists uk_ovfl_arr_5 on gaia_ovfl_arr_5 (id,igrp); create unique index if not exists uk_ovfl_arr_6 on gaia_ovfl_arr_6 (id,igrp); create unique index if not exists uk_ovfl_arr_7 on gaia_ovfl_arr_7 (id,igrp); create unique index if not exists uk_ovfl_arr_8 on gaia_ovfl_arr_8 (id,igrp); create unique index if not exists uk_ovfl_arr_9 on gaia_ovfl_arr_9 (id,igrp); create unique index if not exists uk_ovfl_arr_10 on gaia_ovfl_arr_10 (id,igrp); create unique index if not exists uk_ovfl_arr_11 on gaia_ovfl_arr_11 (id,igrp); create table gaia_coord_arr ( id int ,l0_hp int ,ra_arr double precision[] ,dec_arr double precision[] ,l_arr double precision[] ,b_arr double precision[] ,ecl_lon_arr double precision[] ,ecl_lat_arr double precision[] ,source_id_arr bigint[] ) partition by list (l0_hp) ; create table gaia_coord_arr_0 partition of gaia_coord_arr for values in (0); create table gaia_coord_arr_1 partition of gaia_coord_arr for values in (1); create table gaia_coord_arr_2 partition of gaia_coord_arr for values in (2); create table gaia_coord_arr_3 partition of gaia_coord_arr for values in (3); create table gaia_coord_arr_4 partition of gaia_coord_arr for values in (4); create table gaia_coord_arr_5 partition of gaia_coord_arr for values in (5); create table gaia_coord_arr_6 partition of gaia_coord_arr for values in (6); create table gaia_coord_arr_7 partition of gaia_coord_arr for values in (7); create table gaia_coord_arr_8 partition of gaia_coord_arr for values in (8); create table gaia_coord_arr_9 partition of gaia_coord_arr for values in (9); create table gaia_coord_arr_10 partition of gaia_coord_arr for values in (10); create table gaia_coord_arr_11 partition of gaia_coord_arr for values in (11); begin; insert into gaia_coord_arr select ibhp ,ibhp>>12 from generate_series ((0<<12)+0,(11<<12)+4095) as ibhp ; commit; create index if not exists idx_coord_id_0 on gaia_coord_arr_0 (id); create index if not exists idx_coord_id_1 on gaia_coord_arr_1 (id); create index if not exists idx_coord_id_2 on gaia_coord_arr_2 (id); create index if not exists idx_coord_id_3 on gaia_coord_arr_3 (id); create index if not exists idx_coord_id_4 on gaia_coord_arr_4 (id); create index if not exists idx_coord_id_5 on gaia_coord_arr_5 (id); create index if not exists idx_coord_id_6 on gaia_coord_arr_6 (id); create index if not exists idx_coord_id_7 on gaia_coord_arr_7 (id); create index if not exists idx_coord_id_8 on gaia_coord_arr_8 (id); create index if not exists idx_coord_id_9 on gaia_coord_arr_9 (id); create index if not exists idx_coord_id_10 on gaia_coord_arr_10 (id); create index if not exists idx_coord_id_11 on gaia_coord_arr_11 (id); create table gaia_astro_arr ( id int ,l0_hp int ,parallax_arr double precision[] ,pmra_arr double precision[] ,pmdec_arr double precision[] ,pseudo_color_arr double precision[] ,g_mean_mag_arr real[] ,bp_mean_mag_arr real[] ,rp_mean_mag_arr real[] ,bp_rp_arr real[] ,bp_g_arr real[] ,g_rp_arr real[] ,radial_vel_arr double precision[] ,teff_val_arr real[] ) partition by list (l0_hp) ; create table gaia_astro_arr_0 partition of gaia_astro_arr for values in (0); create table gaia_astro_arr_1 partition of gaia_astro_arr for values in (1); create table gaia_astro_arr_2 partition of gaia_astro_arr for values in (2); create table gaia_astro_arr_3 partition of gaia_astro_arr for values in (3); create table gaia_astro_arr_4 partition of gaia_astro_arr for values in (4); create table gaia_astro_arr_5 partition of gaia_astro_arr for values in (5); create table gaia_astro_arr_6 partition of gaia_astro_arr for values in (6); create table gaia_astro_arr_7 partition of gaia_astro_arr for values in (7); create table gaia_astro_arr_8 partition of gaia_astro_arr for values in (8); create table gaia_astro_arr_9 partition of gaia_astro_arr for values in (9); create table gaia_astro_arr_10 partition of gaia_astro_arr for values in (10); create table gaia_astro_arr_11 partition of gaia_astro_arr for values in (11); begin; insert into gaia_astro_arr select ibhp ,ibhp>>12 from generate_series ((0<<12)+0,(11<<12)+4095) as ibhp ; commit; create index if not exists idx_astro_id_0 on gaia_astro_arr_0 (id); create index if not exists idx_astro_id_1 on gaia_astro_arr_1 (id); create index if not exists idx_astro_id_2 on gaia_astro_arr_2 (id); create index if not exists idx_astro_id_3 on gaia_astro_arr_3 (id); create index if not exists idx_astro_id_4 on gaia_astro_arr_4 (id); create index if not exists idx_astro_id_5 on gaia_astro_arr_5 (id); create index if not exists idx_astro_id_6 on gaia_astro_arr_6 (id); create index if not exists idx_astro_id_7 on gaia_astro_arr_7 (id); create index if not exists idx_astro_id_8 on gaia_astro_arr_8 (id); create index if not exists idx_astro_id_9 on gaia_astro_arr_9 (id); create index if not exists idx_astro_id_10 on gaia_astro_arr_10 (id); create index if not exists idx_astro_id_11 on gaia_astro_arr_11 (id); create table gaia_ids ( id_gida int ,gida_l0_hp int ,gida_arr bigint[] ,gida_org_arr integer[] ) partition by list (gida_l0_hp) ; create table gaia_ids_0 partition of gaia_ids for values in (0); create table gaia_ids_1 partition of gaia_ids for values in (1); create table gaia_ids_2 partition of gaia_ids for values in (2); create table gaia_ids_3 partition of gaia_ids for values in (3); create table gaia_ids_4 partition of gaia_ids for values in (4); create table gaia_ids_5 partition of gaia_ids for values in (5); create table gaia_ids_6 partition of gaia_ids for values in (6); create table gaia_ids_7 partition of gaia_ids for values in (7); create table gaia_ids_8 partition of gaia_ids for values in (8); create table gaia_ids_9 partition of gaia_ids for values in (9); create table gaia_ids_10 partition of gaia_ids for values in (10); create table gaia_ids_11 partition of gaia_ids for values in (11); begin; insert into gaia_ids select ibhp ,ibhp>>12 from generate_series ((0<<12)+0,(11<<12)+4095) as ibhp ; commit; drop table gaia_hp_arr_info; create table gaia_hp_arr_info ( id_ghpa int ,ghpa_l0_hp int ,ghpa_ioffs_arr bigint[] ,ghpa_iseq_arr bigint[] ,ghpa_nseq_overflow_arr int[] ); begin; insert into gaia_hp_arr_info select ibhp ,ibhp>>12 from generate_series ((0<<12)+0,(11<<12)+4095) as ibhp ; commit; -- flags are binary and mean -- 1 (1<<0) -- 2 (1<<1) -- 4 (1<<2) set when ra_hp_min < 90.0 and ra_hp_max > 270.0 drop table gaia_hp_info; create table gaia_hp_info ( id_ghpi int ,ghpi_l0_hp int not null ,ghpi_nsource int ,ghpi_norg_one int ,ghpi_nra_delta_truncated int ,ghpi_ndec_delta_truncated int ,ghpi_ndec_delta_22_truncated int ,ghpi_seq_start int ,ghpi_id_ored bigint ,ghpi_ra_hp_min double precision ,ghpi_ra_hp_max double precision ,ghpi_dec_hp_min double precision ,ghpi_dec_hp_max double precision ,ghpi_ra_substract_base double precision ,ghpi_dec_substract_base double precision ,ghpi_gal_lon_hp_min double precision ,ghpi_gal_lon_hp_max double precision ,ghpi_gal_lat_hp_min double precision ,ghpi_gal_lat_hp_max double precision ,ghpi_ecl_lon_hp_min double precision ,ghpi_ecl_lon_hp_max double precision ,ghpi_ecl_lat_hp_min double precision ,ghpi_ecl_lat_hp_max double precision ,ghpi_ira_correct_old bigint ,ghpi_idec_correct_old bigint ,ghpi_ira_correct bigint ,ghpi_idec_correct bigint ,ghpi_ira_span_max bigint ,ghpi_idec_span_max bigint ,ghpi_imp_date date ,ghpi_flags integer ,ghpi_ra_delta_mul_old integer ,ghpi_dec_delta_mul_old integer ,ghpi_ra_delta_mul integer ,ghpi_dec_delta_mul integer ,ghpi_id_cre_date date ,ghpi_ndec_delta_21_truncated integer ,ghpi_ra_delta_mul_new integer ,ghpi_dec_delta_mul_new integer ,ghpi_dec_delta_ntests integer ,ghpi_dec_delta_best_test integer ,ghpi_ra_delta_ntests integer ,ghpi_ra_delta_best_test integer ,ghpi_dec_greatest_offs integer ,ghpi_ra_greatest_offs integer ,ghpi_dec_n_overflow integer ,ghpi_dec_n_underflow integer ,ghpi_ra_n_overflow integer ,ghpi_ra_n_underflow integer ,ghpi_greatest_ira integer ,ghpi_greatest_idec integer ,ghpi_make_ids_date date ,ghpi_id_gmarr integer ,ghpi_nbit_ira_trunc integer ,ghpi_nbit_idec_trunc integer ) with (fillfactor=60) ; begin; insert into gaia_hp_info select ibhp ,ibhp>>12 from generate_series ((0<<12)+0,(11<<12)+4095) as ibhp ; commit; alter table gaia_hp_info add constraint ghpi_pkey primary key (id_ghpi); alter table gaia_hp_info set (fillfactor=60); cluster gaia_hp_info using ghpi_pkey; create index if not exists brin_ghpi_l0_hp on gaia_hp_info using brin (ghpi_l0_hp) with (pages_per_range=8) ;