Mariadb enum types

[amyfromandi]

Below are the defined enum types within Mariadb. These need to be reviewed to improve the database structure after the Mariadb to Postgresql migration. You can use the query below to pull the enum types that pgloader generated within Postgresql and compare.

Table Name	Column Name	Enum Type
colors	color	enum('','blue','blue dark','blue green','black','yellow','orange','brown dark','brown light','tan','green dark','green light','gray dark','gray light','pink','purple','red','gray','green','brown','steel blue','white')
cols	col_position	enum('','onshore','offshore')
cols	col_type	enum('column','section')
cols	status_code	enum('','active','in process','obsolete')
econs	econ_class	enum('','energy','material','precious commodity','water')
econs	econ_type	enum('','mineral','hydrocarbon','construction','nuclear','coal','aquifer')
environs	environ_class	enum('','marine','non-marine')
environs	environ_type	enum('','carbonate','siliciclastic','fluvial','lacustrine','landscape','glacial','eolian')
intervals	interval_type	enum('supereon','eon','era','period','superepoch','epoch','sub-epoch','age','chron','zone','bin','sub-age','subchron','subzone')
interval_boundaries	boundary_status	enum('','modeled','relative','absolute','spike')
liths	lith_class	enum('','sedimentary','igneous','metamorphic')
liths	lith_group	enum('sandstones','mudrocks','conglomerates','unconsolidated','Folk','Dunham','felsic','mafic','ultramafic')
liths	lith_type	enum('','carbonate','siliciclastic','evaporite','organic','chemical','volcanic','plutonic','metamorphic','sedimentary','igneous','metasedimentary','metaigneous','metavolcanic','regolith','cataclastic')
lith_atts	att_type	enum('','bedform','sed structure','grains','color','lithology','structure')
lookup_measurements	measurement_class	enum('','geophysical','geochemical','sedimentological')
lookup_measurements	measurement_type	enum('','material properties','geochronological','major elements','minor elements','radiogenic isotopes','stable isotopes','petrologic','environmental')
lookup_strat_names	rank	enum('','SGp','Gp','SubGp','Fm','Mbr','Bed')
measurements	measurement_class	enum('','geophysical','geochemical','sedimentological')
measurements	measurement_type	enum('','material properties','geochronological','major elements','minor elements','radiogenic isotopes','stable isotopes','petrologic','environmental')
pbdb_intervals	interval_type	enum('supereon','eon','era','period','superepoch','epoch','sub-epoch','age','chron','zone','bin','sub-age','subchron','subzone')
projects	project	enum('','North America','New Zealand','Deep Sea','Australia','Caribbean','South America','Africa','North American Ediacaran','North American Cretaceous','Indonesia','eODP','Northern Eurasia')
refs	compilation_code	enum('','COSUNA','COSUNA II','Canada','GNS Folio Series 1')
rockd_features	feature_class	enum('','structure','geomorphology')
rockd_features	feature_type	enum('','fault','glacial','deformation')
stats	project	enum('','North America','New Zealand','Deep Sea','Australia','Caribbean','South America','Africa','North American Ediacaran','North American Cretaceous','Indonesia','eODP','Northern Eurasia')
strat_names	rank	enum('','SGp','Gp','SubGp','Fm','Mbr','Bed')
strat_names_lookup	rank	enum('','SGp','Gp','Fm','Mbr','Bed')
strat_tree	rel	enum('','parent','synonym')
structures	structure_class	enum('','fracture','structure','fabric','sedimentology','igneous')
structures	structure_type	enum('','fault','fold','foliation','lineation','paleocurrent','fracture','bedding','contact','intrusion')
tectonics	basin_setting	enum('','divergent','intraplate','convergent','transform','hybrid')
units	color	enum('','blue','blue dark','blue green','black','yellow','orange','brown dark','brown light','tan','green dark','green light','gray dark','gray light','pink','purple','red','gray','green','brown','steel blue','white')
units	outcrop	enum('','surface','subsurface','both')
unit_boundaries	boundary_status	enum('','modeled','relative','absolute','spike')
unit_boundaries	boundary_type	enum('','unconformity','conformity','fault','disconformity','non-conformity','angular unconformity')
unit_contacts	contact	enum('above','below','lateral','lateral-bottom','lateral-top','within')
unit_dates	system	enum('','U/Pb','Rb/Sr','Ar/Ar','C14','Re/Os','K/Ar','Pb/Pb','Fission Track','Amino Acid','Ur-Series')
unit_liths	dom	enum('','dom','sub')
unit_seq_strat	seq_order	enum('','2nd','3rd','4th','5th','6th')
unit_seq_strat	seq_strat	enum('','TST','HST','FSST','LST','SQ')

Postgresql enum types:

SELECT c.table_schema,
       c.table_name,
       c.column_name,
       c.data_type,
       c.udt_name AS enum_name
FROM information_schema.columns c
WHERE c.udt_name IN (
    SELECT t.typname
    FROM pg_type t
    JOIN pg_enum e ON t.oid = e.enumtypid
    JOIN pg_namespace n ON n.oid = t.typnamespace
    WHERE n.nspname = 'macrostrat'
) AND table_schema = 'macrostrat'
ORDER BY c.table_name, c.column_name ASC

[davenquinn]

Just to add – most of these appear to have imported cleanly into the macrostrat schema in PostgreSQL. However, there are some duplicates, mostly from similar tables (e.g., backups of unit_boundaries, etc.) getting duplicate types.

The biggest problem here seems to be the inclusion of the empty strings in most enum types. These mostly appear to be pseudo-nulls and it would be better to be more explicit.