33 lines
1.3 KiB
SQL
33 lines
1.3 KiB
SQL
-- Migration: Update manual_feed_sack_entries to use date-based keys instead of cycle-based
|
|
-- Description: Remove cycle_id and day dependencies, use date + camera_name as unique key
|
|
-- Date: 2026-04-19
|
|
|
|
-- Drop old unique constraint
|
|
ALTER TABLE manual_feed_sack_entries DROP CONSTRAINT IF EXISTS manual_feed_sack_entries_cycle_id_day_camera_name_key;
|
|
|
|
-- Drop foreign key constraint
|
|
ALTER TABLE manual_feed_sack_entries DROP CONSTRAINT IF EXISTS manual_feed_sack_entries_cycle_id_fkey;
|
|
|
|
-- Drop cycle_id and day columns
|
|
ALTER TABLE manual_feed_sack_entries DROP COLUMN IF EXISTS cycle_id;
|
|
ALTER TABLE manual_feed_sack_entries DROP COLUMN IF EXISTS day;
|
|
|
|
-- Add new unique constraint on date + camera_name (only if it doesn't exist)
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conname = 'manual_feed_sack_entries_date_camera_key'
|
|
) THEN
|
|
ALTER TABLE manual_feed_sack_entries
|
|
ADD CONSTRAINT manual_feed_sack_entries_date_camera_key UNIQUE (date, camera_name);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Drop old index on cycle_id
|
|
DROP INDEX IF EXISTS idx_manual_feed_sack_cycle;
|
|
DROP INDEX IF EXISTS idx_manual_feed_sack_day;
|
|
|
|
-- Add index on date for faster lookups
|
|
CREATE INDEX IF NOT EXISTS idx_manual_feed_sack_date ON manual_feed_sack_entries(date);
|