I was trying to migrate production environment to staging.
Miserably failing first time overlooking primary keys ending up being text instead of UUID
I wiped staging db clean. But trying to migrate schemas got this error.
Double checked, NO tables in staging. What is it am I doing wrong again?
CREATE TYPE asset_type_enum_da79c719 AS ENUM ('Stationary Buoy', 'Quadruped', 'ROV', 'Subsurface Sensor', 'Drift Buoy', 'Weather Station', 'ASV Mascat', 'ASV BlueBoat', 'ASV Mako', 'Jaibot Hydro', 'UAV Drone', 'UAV Wing');
CREATE TYPE modem_type_enum_faf3bea5 AS ENUM ('SWARM', 'LTE', '4G', 'Lo-Ra', 'Wi-Fi', '433 Mhz Radio', '868 Mhz Radio');
CREATE TYPE signed_enum_ae22341d AS ENUM ('float', 'signed', 'unsigned', 'bool');
CREATE TYPE status_enum_1bb2e76d AS ENUM ('Scientist', 'Project Lead', 'iSENSYS Employee');
CREATE TYPE unit_status_enum_97771b29 AS ENUM ('Unassigned', 'OFFLINE', 'Not Deployed', 'Idling', 'Collecting Data', 'Traveling to', 'Returning home', 'Following Pattern', 'ALARM', 'DRIFTING');
CREATE TYPE sensor_type_enum_ebc44b32 AS ENUM ('Weather Station', 'System', 'Water Quality', 'Waves', 'Current', 'Chemical', 'Oil', 'Location', 'Plant', 'Audio-Visual');
DROP FUNCTION IF EXISTS get_days_since_last_non_zero_metric;
CREATE OR REPLACE FUNCTION public.get_days_since_last_non_zero_metric(current_project_id integer, metric_column_name text)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
max_date TIMESTAMP;
days_since_last_message INTEGER;
BEGIN
EXECUTE 'SELECT COALESCE(MAX(' || metric_column_name || '), NOW()) FROM asset_data WHERE asset_id IN (SELECT id FROM assets_db WHERE project_id = $1) AND ' || metric_column_name || ' <> ''0'''
INTO max_date
USING current_project_id;
SELECT EXTRACT(day FROM AGE(NOW(), max_date)) INTO days_since_last_message;
RETURN days_since_last_message;
END;
$function$
;
CREATE TABLE projects_db (
id int4 NOT NULL,
project text,
dri text,
status text,
expected_launch_date date,
description text,
picture text,
latitude numeric DEFAULT '0'::numeric,
longitude numeric DEFAULT '0'::numeric,
ph_min numeric DEFAULT '0'::numeric,
ph_max numeric DEFAULT '0'::numeric,
wave_height_min numeric DEFAULT '0'::numeric,
wave_height_max numeric DEFAULT '0'::numeric,
signal_to_noise_min numeric DEFAULT '0'::numeric,
signal_to_noise_max numeric DEFAULT '0'::numeric,
messages_in_queue_min numeric DEFAULT '0'::numeric,
messages_in_queue_max numeric DEFAULT '0'::numeric,
current_min numeric DEFAULT '0'::numeric,
current_max numeric DEFAULT '0'::numeric,
battery_voltage_min numeric DEFAULT '0'::numeric,
battery_voltage_max numeric DEFAULT '0'::numeric,
ambient_light_min numeric DEFAULT '0'::numeric,
ambient_light_max numeric DEFAULT '0'::numeric,
temperature_min numeric DEFAULT '0'::numeric,
temperature_max numeric DEFAULT '0'::numeric,
orp_min numeric DEFAULT '0'::numeric,
orp_max numeric DEFAULT '0'::numeric,
turbidity_min numeric DEFAULT '0'::numeric,
turbidity_max numeric DEFAULT '0'::numeric,
transmissivity_min numeric DEFAULT '0'::numeric,
transmissivity_max numeric DEFAULT '0'::numeric,
dissolved_oxygenen_concentration_min numeric DEFAULT '0'::numeric,
dissolved_oxygenen_concentration_max numeric DEFAULT '0'::numeric,
dissolved_oxygen_saturation_min numeric DEFAULT '0'::numeric,
dissolved_oxygen_saturation_max numeric DEFAULT '0'::numeric,
depth_min numeric DEFAULT '0'::numeric,
depth_max numeric DEFAULT '0'::numeric,
barometric_pressure_min numeric DEFAULT '0'::numeric,
barometric_pressure_max numeric DEFAULT '0'::numeric,
total_dissolved_gas_min numeric DEFAULT '0'::numeric,
total_dissolved_gas_max numeric DEFAULT '0'::numeric,
chlorophyll_blue_min numeric DEFAULT '0'::numeric,
chlorophyll_blue_max numeric DEFAULT '0'::numeric,
chlorophyll_a_red_min numeric DEFAULT '0'::numeric,
chlorophyll_a_red_max numeric DEFAULT '0'::numeric,
rhodamine_dye_min numeric DEFAULT '0'::numeric,
rhodamine_dye_max numeric DEFAULT '0'::numeric,
phycocyanin_min numeric DEFAULT '0'::numeric,
phycocyanin_max numeric DEFAULT '0'::numeric,
cdom_fdom_min numeric DEFAULT '0'::numeric,
cdom_fdom_max numeric DEFAULT '0'::numeric,
optical_brightener_min numeric DEFAULT '0'::numeric,
optical_brightener_max numeric DEFAULT '0'::numeric,
tryptophan_min numeric DEFAULT '0'::numeric,
tryptophan_max numeric DEFAULT '0'::numeric,
fluorescein_dye_min numeric DEFAULT '0'::numeric,
fluorescein_dye_max numeric DEFAULT '0'::numeric,
ptsa_min numeric DEFAULT '0'::numeric,
ptsa_max numeric DEFAULT '0'::numeric,
refined_oil_min numeric DEFAULT '0'::numeric,
refined_oil_max numeric DEFAULT '0'::numeric,
crude_oil_min numeric DEFAULT '0'::numeric,
crude_oil_max numeric DEFAULT '0'::numeric,
ammonium_min numeric DEFAULT '0'::numeric,
ammonium_max numeric DEFAULT '0'::numeric,
nitrate_min numeric DEFAULT '0'::numeric,
nitrate_max numeric DEFAULT '0'::numeric,
chloride_min numeric DEFAULT '0'::numeric,
chloride_max numeric DEFAULT '0'::numeric,
sodium_min numeric DEFAULT '0'::numeric,
sodium_max numeric DEFAULT '0'::numeric,
calcium_min numeric DEFAULT '0'::numeric,
calcium_max numeric DEFAULT '0'::numeric,
bromide_min numeric DEFAULT '0'::numeric,
bromide_max numeric DEFAULT '0'::numeric,
photometric_par_min numeric DEFAULT '0'::numeric,
photometric_par_max numeric DEFAULT '0'::numeric,
carbon_dioxide_min numeric DEFAULT '0'::numeric,
carbon_dioxide_max numeric DEFAULT '0'::numeric,
wind_speed_min numeric DEFAULT '0'::numeric,
wind_speed_max numeric DEFAULT '0'::numeric,
wind_direction_min numeric DEFAULT '0'::numeric,
wind_direction_max numeric DEFAULT '0'::numeric,
air_temperature_min numeric DEFAULT '0'::numeric,
air_temperature_max numeric DEFAULT '0'::numeric,
wind_chill_temperature_min numeric DEFAULT '0'::numeric,
wind_chill_temperature_max numeric DEFAULT '0'::numeric,
relative_humidity_min numeric DEFAULT '0'::numeric,
relative_humidity_max numeric DEFAULT '0'::numeric,
dew_point_temperature_min numeric DEFAULT '0'::numeric,
dew_point_temperature_max numeric DEFAULT '0'::numeric,
magnetic_compass_heading_min numeric DEFAULT '0'::numeric,
magnetic_compass_heading_max numeric DEFAULT '0'::numeric,
heading_relative_to_true_north_min numeric DEFAULT '0'::numeric,
heading_relative_to_true_north_max numeric DEFAULT '0'::numeric,
latitude_min numeric DEFAULT '0'::numeric,
latitude_max numeric DEFAULT '0'::numeric,
longitude_min numeric DEFAULT '0'::numeric,
longitude_max numeric DEFAULT '0'::numeric,
timezone int4 DEFAULT 0,
CONSTRAINT projects_pkey PRIMARY KEY (id)
);
CREATE TABLE users_db (
id SERIAL,
created_at timestamptz DEFAULT now(),
first_name text,
last_name text,
email text,
phone text,
notes text,
status status_enum_1bb2e76d,
picture text,
CONSTRAINT users_db_pkey PRIMARY KEY (id)
);
CREATE TABLE assets_db (
id SERIAL,
created_at timestamptz DEFAULT now(),
asset_type asset_type_enum_da79c719,
asset_name text,
custodian_user_id int4,
project_id int4,
latitude numeric DEFAULT '0'::numeric,
longitude numeric DEFAULT '0'::numeric,
firmware text,
deployment_date date DEFAULT now(),
photo text,
alarm_drift bool DEFAULT false,
alarm_impact bool DEFAULT false,
alarm_tilt bool DEFAULT false,
propolsion_alarm bool DEFAULT false,
status_solar_carging bool DEFAULT false,
status_led bool DEFAULT false,
modem_list jsonb DEFAULT '{}'::jsonb,
sensor_list jsonb DEFAULT '{}'::jsonb,
alarm_leak bool DEFAULT false,
unit_status unit_status_enum_97771b29,
icon text,
CONSTRAINT assets_db_pkey PRIMARY KEY (id),
CONSTRAINT assets_db_custodian_user_id_fkey FOREIGN KEY (custodian_user_id) REFERENCES users_db (id),
CONSTRAINT assets_db_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects_db (id)
);
CREATE TABLE modem_db (
id SERIAL,
modem_type modem_type_enum_faf3bea5,
address int4 DEFAULT 0,
login text,
password text,
management_console text,
notes text,
photo text,
in_use bool DEFAULT false,
asset_deployed_at int4,
CONSTRAINT modem_db_pkey PRIMARY KEY (id),
CONSTRAINT modem_db_asset_deployed_at_fkey FOREIGN KEY (asset_deployed_at) REFERENCES assets_db (id)
);
CREATE TABLE hex_db (
id SERIAL,
created_at timestamp DEFAULT now(),
asset_id float8 DEFAULT '0'::double precision,
swarm_message_id int8 DEFAULT 0,
hex_message text,
CONSTRAINT hex_db_pkey PRIMARY KEY (id)
);
CREATE TABLE sensor_db (
id SERIAL,
sensot_type text,
sensor_notes text,
days_between_calibration int4 DEFAULT 90,
sereal_number text,
in_use bool DEFAULT false,
data_quality int4 DEFAULT 0,
last_calibration date DEFAULT now(),
photo text,
metrics jsonb DEFAULT '{}'::jsonb,
asset_deployed_at int4,
metrics_captured jsonb DEFAULT '{}'::jsonb,
sensor_type sensor_type_enum_ebc44b32,
CONSTRAINT sensor_types_db_pkey PRIMARY KEY (id),
CONSTRAINT sensor_db_asset_deployed_at_fkey FOREIGN KEY (asset_deployed_at) REFERENCES assets_db (id),
CONSTRAINT sensor_db_sereal_number_key UNIQUE(sereal_number)
);
CREATE TABLE metrics_db (
id SERIAL,
scaling float8 DEFAULT '0'::numeric,
units text,
description text,
"offset" float8 DEFAULT '0'::double precision,
signed signed_enum_ae22341d,
size int2 DEFAULT '0'::smallint,
column_name text,
image text,
image_dark text,
resolution int2 DEFAULT '0'::smallint,
device_id int4,
CONSTRAINT metrics_db_pkey PRIMARY KEY (id),
CONSTRAINT metrics_db_device_id_fkey FOREIGN KEY (device_id) REFERENCES sensor_db (id)
);
CREATE TABLE raw_message_db (
id SERIAL,
created_at timestamptz DEFAULT now(),
raw_message text,
asset_id int4 DEFAULT 0,
swarm_message_id int8 DEFAULT 0,
CONSTRAINT raw_message_db_pkey PRIMARY KEY (id)
);
CREATE TABLE struct_db (
id SERIAL,
class int4 DEFAULT 0,
payload text,
CONSTRAINT struct_db_pkey PRIMARY KEY (id)
);
CREATE TABLE asset_data (
id SERIAL,
created_at timestamptz DEFAULT now(),
timestamp timestamptz DEFAULT now(),
latitude float8 DEFAULT '0'::double precision,
longitude float8 DEFAULT '0'::double precision,
wave_height float8,
signal_to_noise int8,
messages_in_queue int8,
current numeric,
battery_voltage float8 DEFAULT '0'::numeric,
ambient_light int8,
temperature numeric,
ph numeric,
orp int8,
turbidity int8,
transmissivity int8,
dissolved_oxygenen_concentration int8,
dissolved_oxygen_saturation int8,
depth numeric,
barometric_pressure float8,
total_dissolved_gas int8,
chlorophyll_blue int8,
chlorophyll_a_red int8,
rhodamine_dye int8,
phycocyanin float8,
cdom_fdom int8,
optical_brightener float8,
tryptophan int8,
fluorescein_dye int8,
ptsa float8,
refined_oil int8,
crude_oil float8,
ammonium int8,
nitrate int8,
chloride float8,
sodium float8,
calcium float8,
bromide float8,
photometric_par int8,
carbon_dioxide int8,
asset_id int4,
swarm_message_id int8,
modem_id int8 DEFAULT '0'::bigint,
wind_speed float8,
wind_direction int4,
air_temperature float8 DEFAULT '0'::numeric,
relative_humidity int4,
dew_point_temperature int4,
magnetic_compass_heading int4,
heading_relative_to_true_north int4,
wind_gust float8,
wind_lull float8,
uv float8,
precip int4,
lightning_strike_last_distance int4,
lightning_strike_count int4,
timezone text DEFAULT 'America/New_York'::text,
CONSTRAINT buoy_data_pkey PRIMARY KEY (id),
CONSTRAINT asset_data_asset_id_fkey FOREIGN KEY (asset_id) REFERENCES assets_db (id)
);
CREATE TABLE sample_users (
id SERIAL,
name text,
email text,
signup_date timestamp,
role text,
enabled bool,
CONSTRAINT sample_users_pkey PRIMARY KEY (id)
);
CREATE TABLE mobile_app_sample_data (
id int8 NOT NULL,
name text,
email text,
sales int8,
image text,
CONSTRAINT mobile_app_sample_data_pkey PRIMARY KEY (id)
);