(Beta) MPC Database Tables Schema

The MPC makes its PostgreSQL database of observations and orbits available for replication via the SBN. Displayed below are two tables.
  1. The first table lists the tables and a description of its contents.
  2. The second table displayed lists each table and each field within each table and a description of it.
A machine-readable format of the table and its content description can be downloaded here.
A machine-readable format of each table and its schema can be downloaded here.
To order the columns click the header to sort the data.

Last Updated: 2023-09-30

Table NameDescription
obsNone
neocp_eventsNone
current_identificationsAll single-designations, and all identifications between designations. Always uses primary provisional designation (even for numbered objects). Includes all comets and satellites.
neocp_varNone
neocp_prev_desNone
neocp_obs_archiveNone
neocp_obsNone
orbfit_resultsIt is intended that the fields in this table will contain the immediate results from orbifits, in the as-supplied json/dictionary format. It is intended that these fields will ... (i) Be supplied back to the orbit-fit code on subsequent calls (rwo_json and mid_epoch_json are the two that would be supplied) (ii) Be expanded out (trigger? generated-fields?) to populate detailed tables (rwo_json, mid_epoch_json, stamndard_epoch_json, quality_json would all be expanded out)
obs_alterations_correctionsIt is intended that the fields in this table will record updates that have been made to the obs table that require subsequent publication to announce UNPUBLISH/UNASSOCIATION/SEND-TO-ITF Some/all of these changes may be indicated in the obs table itself (e.g. via status flags), but this table is intended to help flag changes to observations that require publication in some form.
obs_alterations_redesignationsIt is intended that the fields in this table will record updates that have been made to the obs table that require subsequent publication to announce REDESIGNATIONS These changes may be indicated in the obs table itself (e.g. via status flags), but this table is intended to help flag changes to observations that require publication in some form.
obs_alterations_deletionsIt is intended that the fields in this table will record updates that have been made to the obs table that require subsequent publication to announce DELETION Some/all of these changes may be indicated in the obs table itself (e.g. via status flags), but this table is intended to help flag changes to observations that require publication in some form.
obs_alterations_unassociationsIt is intended that the fields in this table will record updates that have been made to the obs table that require subsequent publication to announce UNPUBLISH/UNASSOCIATION/SEND-TO-ITF Some/all of these changes may be indicated in the obs table itself (e.g. via status flags), but this table is intended to help flag changes to observations that require publication in some form.
neocp_elsNone
numbered_identificationsNumbers and Names for any objects that have been Numbered or Named. Linked to primary-provisional-designation in current_identifications
primary_objectsAll Objects Live Here: Labelled by their primary provisional designation. Fields indicate the tables in which any orbit information might be found

Last Updated: 2023-09-30

Table NameColumnData TypeDateTime Precision (if applicable)Description
current_identificationsidint4NoneNone
current_identificationspacked_primary_provisional_designationtextNoneThe primary provisional designation in packed form (e.g. K08A00B)
current_identificationspacked_secondary_provisional_designationtextNoneThe secondary provisional designation in packed form (e.g. K08A00B). May be the same-as (A=A) or different-to (A=B) the primary provisional designation
current_identificationsunpacked_primary_provisional_designationtextNoneThe primary provisional designation in unpacked form (e.g. 2008 AB)
current_identificationsunpacked_secondary_provisional_designationtextNoneThe secondary provisional designation in unpacked form (e.g. 2008 AB). May be the same-as (A=A) or different-to (A=B) the primary provisional designation
current_identificationspublishedint4NoneHas this been published yet? i.e. has it been published in the DOU, Mid month, or monthly products. Field values: 0=unpublished ; 1=published as MPEC; 2=published in DOU ; 3=published in mid-month ; 4=published in monthly
current_identificationsidentifier_ids_textNoneThis is a set of unique identifier_ids in an array e.g. {1e96e1ba-63be-11ea-8e74-801844df97e0,1eb8bdee-63be-11ea-8e74-801844df97e0} that can link to multiple records in the identification_metadata table. Multiple people can suggest A=B and this would be recorded in the identification_metadata table. This is important for the backfill when reading the flat files. So in the backfill scenario, if the record exists and it has a different name and/or reference then 1. add to the id_history, then add to metadata, then UPDATE columns publication_references, identifier_ids in current_identifications otherwise if no record exists create one
current_identificationsobject_typeint4NoneInteger to indicate the object type. To be linked (foreign key) to object_type lookup table
current_identificationsnumberedboolNoneHas the object been numbered and hence does it appear in the numbered_objects table?
current_identificationscreated_attimestamp6None
current_identificationsupdated_attimestamp6None
mpc_orbitsidint4NoneNone
mpc_orbitspacked_primary_provisional_designationtextNoneThe primary provisional designation in packed form (e.g. K08A00B). UNIQUE
mpc_orbitsunpacked_primary_provisional_designationtextNoneThe primary provisional designation in UNpacked form (e.g. 2008 AB). UNIQUE
mpc_orbitsmpc_orb_jsonbjsonbNoneNone
mpc_orbitscreated_attimestamp6None
mpc_orbitsupdated_attimestamp6None
neocp_elsidint4NoneNone
neocp_elsdesigvarcharNoneNone
neocp_elselsvarcharNoneNone
neocp_elsdsc_obsvarcharNoneNone
neocp_elsdigest2numericNoneNone
neocp_elsflagbpcharNoneNone
neocp_elsprepbpcharNoneNone
neocp_elscometbpcharNoneNone
neocp_elscreated_attimestamp6None
neocp_elsupdated_attimestamp6None
neocp_eventsidint4NoneNone
neocp_eventsdesigtextNoneNone
neocp_eventsevent_typetextNoneNone
neocp_eventsevent_texttextNoneNone
neocp_eventsevent_usertextNoneNone
neocp_eventscreated_attimestamp6None
neocp_eventsupdated_attimestamp6None
neocp_obsidint4NoneNone
neocp_obsdesigvarcharNoneNone
neocp_obstrkidtextNoneNone
neocp_obsobs80varcharNoneNone
neocp_obsrmstimenumericNoneNone
neocp_obsrmsranumericNoneNone
neocp_obsrmsdecnumericNoneNone
neocp_obsrmscorrnumericNoneNone
neocp_obsforce_codetextNoneNone
neocp_obscreated_attimestamp6None
neocp_obsupdated_attimestamp6None
neocp_obs_archiveidint4NoneNone
neocp_obs_archivedesigvarcharNoneNone
neocp_obs_archivetrkidtextNoneNone
neocp_obs_archiveobs80varcharNoneNone
neocp_obs_archivermstimenumericNoneNone
neocp_obs_archivermsranumericNoneNone
neocp_obs_archivermsdecnumericNoneNone
neocp_obs_archivermscorrnumericNoneNone
neocp_obs_archiveforce_codetextNoneNone
neocp_obs_archivecreated_attimestamp6None
neocp_obs_archiveupdated_attimestamp6None
neocp_prev_desidint4NoneNone
neocp_prev_desdesigtextNoneNone
neocp_prev_desstatustextNoneNone
neocp_prev_desiau_desigtextNoneNone
neocp_prev_despkd_desigtextNoneNone
neocp_prev_desreftextNoneNone
neocp_prev_desdigest2numericNoneNone
neocp_prev_descreated_attimestamp6None
neocp_prev_desupdated_attimestamp6None
neocp_varidint4NoneNone
neocp_vardesigvarcharNoneNone
neocp_varelsvarcharNoneNone
neocp_varcreated_attimestamp6None
neocp_varupdated_attimestamp6None
numbered_identificationsidint4NoneNone
numbered_identificationspacked_primary_provisional_designationtextNoneKeeping a map of packed (K03B35W) and unpacked (2003 BW35)
numbered_identificationsunpacked_primary_provisional_designationtextNoneKeeping a map of packed (K03B35W) and unpacked (2003 BW35)
numbered_identificationspermidtextNoneThe ADES version of the permanent ID which ... follows the associated IAU designation very closely, with the exception of removing parentheses around the permanent designation of minor planets ... For natural satellites, the IAU designation is preserved except that in permanent designations arabic numerals are used instead of roman numerals and for permanent designations of satellites of minor planets the name of the central body is neglected. Could be 541130 (Minor Planet), 1 (Minor Planet), 257P (Comet), 73P-AC (Comet Fragment), Jupiter 13 (Satellite - of Jupiter)
numbered_identificationsiau_designationtextNoneNone
numbered_identificationsiau_nametextNoneFor example, 1 = Ceres, 57567 = Crikey
numbered_identificationsnumbered_publication_references_textNoneArray of references to any MPC publication(s) that concern the numbering of this object (e.g. Monthly-MPC, etc)
numbered_identificationsnamed_publication_references_textNoneNone
numbered_identificationsnaming_credittextNoneNone
numbered_identificationscreated_attimestamp6None
numbered_identificationsupdated_attimestamp6None
obs_alterations_correctionsidint4NoneNone
obs_alterations_correctionsobsid_oldtextNoneThe unique obsid of the OLD observation that was REPLACED
obs_alterations_correctionsobsid_newtextNoneThe unique obsid of the NEW observation that REPLACES the obsid_old
obs_alterations_correctionspublication_ref_textNoneArray of reference(s) to pubn(s) announcing this unassociation
obs_alterations_correctionscreated_attimestamp6None
obs_alterations_correctionsupdated_attimestamp6None
obs_alterations_deletionsidint4NoneNone
obs_alterations_deletionsobsidtextNoneThe unique obsid of the observation that was altered. Links to the observation record in the obs table.
obs_alterations_deletionspublication_ref_textNoneArray of reference(s) to pubn(s) announcing this deletion
obs_alterations_deletionsstatusint4NoneInteger to indicate state of publication. 0=Unpublished/Needs-to-be-published, 1=Published in DOU, 2=Published in Monthly
obs_alterations_deletionscreated_attimestamp6None
obs_alterations_deletionsupdated_attimestamp6None
obs_alterations_redesignationsidint4NoneNone
obs_alterations_redesignationsobsidtextNoneThe unique obsid of the observation that was altered. Links to the observation record in the obs table.
obs_alterations_redesignationspacked_provisional_designation_fromtextNoneOriginal packed provisional designation
obs_alterations_redesignationsunpacked_provisional_designation_fromtextNoneOriginal unpacked provisional designation
obs_alterations_redesignationspacked_provisional_designation_totextNoneNew packed provisional designation
obs_alterations_redesignationsunpacked_provisional_designation_totextNoneNew unpacked provisional designation
obs_alterations_redesignationspublication_ref_textNoneArray of reference(s) to pubn(s) announcing this redesignation
obs_alterations_redesignationsstatusint4NoneInteger to indicate state of publication. 0=Unpublished/Needs-to-be-published, 1=Published in DOU, 2=Published in Monthly
obs_alterations_redesignationscreated_attimestamp6None
obs_alterations_redesignationsupdated_attimestamp6None
obs_alterations_redesignationsnew_designation_createdboolNoneNone
obs_alterations_unassociationsidint4NoneNone
obs_alterations_unassociationsobsidtextNoneThe unique obsid of the observation that was altered. Links to the observation record in the obs table.
obs_alterations_unassociationsunpacked_provisional_designation_fromtextNoneNone
obs_alterations_unassociationspacked_provisional_designation_fromtextNoneNone
obs_alterations_unassociationstrkmpc_totextNoneNew trkMPC used to label the observations within the ITF
obs_alterations_unassociationspublication_ref_textNoneArray of reference(s) to pubn(s) announcing this unassociation
obs_alterations_unassociationscreated_attimestamp6None
obs_alterations_unassociationsupdated_attimestamp6None
obs_sbnidint4NoneNone
obs_sbntrksubtextNoneNone
obs_sbntrkidtextNoneNone
obs_sbnobsidtextNoneNone
obs_sbnsubmission_idtextNoneNone
obs_sbnsubmission_block_idtextNoneNone
obs_sbnobs80textNoneNone
obs_sbnstatusbpcharNoneNone
obs_sbnreftextNoneNone
obs_sbnhealpixint8NoneNone
obs_sbnpermidtextNoneNone
obs_sbnprovidtextNoneNone
obs_sbnartsattextNoneNone
obs_sbnmodetextNoneNone
obs_sbnstntextNoneNone
obs_sbntrxtextNoneNone
obs_sbnrcvtextNoneNone
obs_sbnsystextNoneNone
obs_sbnctrint4NoneNone
obs_sbnpos1numericNoneNone
obs_sbnpos2numericNoneNone
obs_sbnpos3numericNoneNone
obs_sbnposcov11numericNoneNone
obs_sbnposcov12numericNoneNone
obs_sbnposcov13numericNoneNone
obs_sbnposcov22numericNoneNone
obs_sbnposcov23numericNoneNone
obs_sbnposcov33numericNoneNone
obs_sbnprogtextNoneNone
obs_sbnobstimetimestamp6None
obs_sbnranumericNoneNone
obs_sbndecnumericNoneNone
obs_sbnrastarnumericNoneNone
obs_sbndecstarnumericNoneNone
obs_sbnobscentertextNoneNone
obs_sbndeltaranumericNoneNone
obs_sbndeltadecnumericNoneNone
obs_sbndistnumericNoneNone
obs_sbnpanumericNoneNone
obs_sbnrmsranumericNoneNone
obs_sbnrmsdecnumericNoneNone
obs_sbnrmsdistnumericNoneNone
obs_sbnrmspanumericNoneNone
obs_sbnrmscorrnumericNoneNone
obs_sbndelaynumericNoneNone
obs_sbnrmsdelaynumericNoneNone
obs_sbndopplernumericNoneNone
obs_sbnrmsdopplernumericNoneNone
obs_sbnastcattextNoneNone
obs_sbnmagnumericNoneNone
obs_sbnrmsmagnumericNoneNone
obs_sbnbandtextNoneNone
obs_sbnphotcattextNoneNone
obs_sbnphotapnumericNoneNone
obs_sbnnucmagint2NoneNone
obs_sbnlogsnrnumericNoneNone
obs_sbnseeingnumericNoneNone
obs_sbnexpnumericNoneNone
obs_sbnrmsfitnumericNoneNone
obs_sbncomint2NoneNone
obs_sbnfrqnumericNoneNone
obs_sbndiscbpcharNoneNone
obs_sbnsubfrmtextNoneNone
obs_sbnsubfmttextNoneNone
obs_sbnprectimeint4NoneNone
obs_sbnprecranumericNoneNone
obs_sbnprecdecnumericNoneNone
obs_sbnunctimenumericNoneNone
obs_sbnnotestextNoneNone
obs_sbnremarkstextNoneNone
obs_sbndeprecatedbpcharNoneNone
obs_sbnlocalusetextNoneNone
obs_sbnnstarsint4NoneNone
obs_sbnprev_desigtextNoneNone
obs_sbnprev_reftextNoneNone
obs_sbnrmstimenumericNoneNone
obs_sbncreated_attimestamptz6None
obs_sbnupdated_attimestamptz6None
obs_sbntrkmpctextNoneNone
obs_sbnorbit_idtextNoneNone
obs_sbndesignation_asteriskboolNoneNone
obs_sbnall_pub_ref_textNoneNone
obs_sbnshapeoccboolNoneNone
obs_sbnobssubidtextNoneNone
obs_sbnreplacesobsidtextNoneNone
obs_sbngroup_idtextNoneNone
primary_objectsidint4NoneNone
primary_objectspacked_primary_provisional_designationtextNoneThe primary provisional designation in packed form (e.g. K08A00B). UNIQUE
primary_objectsunpacked_primary_provisional_designationtextNoneThe primary provisional designation in UNpacked form (e.g. 2008 AB). UNIQUE
primary_objectsstatusint4Nonegranular status of orbfit results
primary_objectsstandard_minor_planetboolNoneBoolean to indicate whether the object-orbit is specified in the standard_minor_planet table
primary_objectsstandard_epochboolNoneIf in standard_minor_planet, boolean indicates whether orbit at standard-epoch is populated
primary_objectsorbfit_epochboolNoneIf in standard_minor_planet, boolean indicates whether orbit at mid-observation-epoch is populated
primary_objectsnongravsboolNoneBoolean to indicate whether the object-orbit is specified in the nongravs table
primary_objectssatelliteboolNoneBoolean to indicate whether the object-orbit is specified in the satellite table
primary_objectscometboolNoneBoolean to indicate whether the object-orbit is specified in the comet table
primary_objectsbarycentricboolNoneBoolean to indicate whether the object-orbit is specified in the barycentric table. N.B. Can be in standard-table AND barycentric table
primary_objectsno_orbitboolNoneIf no orbit calculated: new insert; no-possible calc; ...
primary_objectsorbit_publication_references_textNoneArray of references to MPC publication(s) that contained this particular orbit calculation (e.g. DOU MPEC, mid-month, Monthly-MPC, etc)
primary_objectsflag_all_object_obs_consistentboolNoneflag if/when all observations for an object have been checked to be consistent with obs-files
primary_objectsflag_orbit_calculated_from_consistent_obsboolNoneWas this orbit calculated using obs flagged as consistent ? (may be necessary while developing / transitioning)
primary_objectsflag_allowed_externalboolNoneIf flag_all_object_obs_consistent=True and flag_orbit_calculated_from_consistent_obs=True, then set flag_allowed_external = True
primary_objectscreated_attimestamp6None
primary_objectsupdated_attimestamp6None
primary_objectsorbit_publishedint4NoneHas this ORBIT (not object-desig) been published yet? i.e. has it been published in the DOU, Mid month, or monthly products. Field values: 0=unpublished ; 1=published as MPEC; 2=published in DOU ; 3=published in mid-month ; 4=published in monthly
primary_objectsobject_typeint4NoneNone