`db/migrations/018_widen_sighting_uniq.sql`

26 lines
-- Widen the restaurant_sighting unique key to include seen_date.
--
-- Migration 017 created `uniq_sighting_natural` on
--   (restaurant_id, person_name, COALESCE(era, ''))
-- which would silently collapse legitimately-different-dated sightings of the
-- same patron at the same restaurant (e.g. dated tabloid sightings vs. an
-- era-only "regular in the 60s"). The schema explicitly supports `seen_date`
-- for specific-date sightings (per migration 016), so the natural key must
-- include it.
--
-- This migration drops the old index and creates a wider one. No rows are
-- deleted (017 already deduped what was there at the time, and any new dated
-- sightings since are correctly considered distinct).

DROP INDEX IF EXISTS uniq_sighting_natural;

-- Use a date sentinel rather than ::text cast — date_to_text isn't IMMUTABLE
-- under all collations. Sentinel is far enough from any plausible sighting.
CREATE UNIQUE INDEX IF NOT EXISTS uniq_sighting_natural
  ON restaurant_sighting (
    restaurant_id,
    person_name,
    COALESCE(era, ''),
    COALESCE(seen_date, DATE '1900-01-01')
  );