30 lines
1.4 KiB
SQL
30 lines
1.4 KiB
SQL
-- Migration: Add dedicated columns for business info
|
|
-- Purpose: Move business data from metadata JSONB to queryable/indexable columns
|
|
-- Date: 2026-01-31
|
|
|
|
-- Add business info columns
|
|
ALTER TABLE jobs ADD COLUMN IF NOT EXISTS business_name VARCHAR(500);
|
|
ALTER TABLE jobs ADD COLUMN IF NOT EXISTS business_category VARCHAR(255);
|
|
ALTER TABLE jobs ADD COLUMN IF NOT EXISTS business_address TEXT;
|
|
ALTER TABLE jobs ADD COLUMN IF NOT EXISTS business_rating NUMERIC(3,2);
|
|
|
|
-- Add indexes for common queries
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_business_name ON jobs(business_name);
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_business_category ON jobs(business_category);
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_business_rating ON jobs(business_rating);
|
|
|
|
-- Migrate existing data from metadata JSONB to new columns
|
|
UPDATE jobs SET
|
|
business_name = metadata->>'business_name',
|
|
business_address = metadata->>'business_address',
|
|
business_rating = CASE
|
|
WHEN metadata->>'rating_snapshot' IS NOT NULL
|
|
THEN (metadata->>'rating_snapshot')::NUMERIC(3,2)
|
|
ELSE NULL
|
|
END
|
|
WHERE metadata IS NOT NULL
|
|
AND (business_name IS NULL OR business_address IS NULL OR business_rating IS NULL);
|
|
|
|
-- Clean up metadata: remove migrated fields (optional - keeps metadata for performance metrics only)
|
|
-- Note: We keep the data in metadata for backward compatibility, but new code should use columns
|