Slide 33
Slide 33 text
WITH moved_rows AS (
DELETE FROM argo_measurements am
WHERE filename = '#{filename}'
AND latitude IS NOT NULL
AND longitude IS NOT NULL
AND temperature_in_celsius IS NOT NULL
AND depth_in_meters IS NOT NULL
RETURNING am.*
)
INSERT INTO
measurements (source, source_id, latitude, longitude, depth_in_meters, temperature_in_celsius, measured_on, created_at, updated_at, location)
SELECT
source,
source_id,
latitude,
longitude,
depth_in_meters,
temperature_in_celsius,
measured_on,
current_timestamp,
current_timestamp,
ST_SetSRID(ST_MakePoint(longitude, latitude),4326) as location
FROM moved_rows
ON CONFLICT (latitude, longitude, depth_in_meters, measured_on) DO NOTHING