mahyargdz
28 lines
998 B
SQL
28 lines
998 B
SQL
-- Update push notification token field to support multiple devices
|
|
-- Step 1: Add new push_tokens JSON array column
|
|
ALTER TABLE users ADD COLUMN push_tokens JSONB DEFAULT NULL;
|
|
|
|
-- Step 2: Migrate existing single push_token data to the new array format
|
|
UPDATE users
|
|
SET
|
|
push_tokens = CASE
|
|
WHEN push_token IS NOT NULL
|
|
AND push_token != '' THEN jsonb_build_array (push_token)
|
|
ELSE NULL
|
|
END
|
|
WHERE
|
|
push_token IS NOT NULL;
|
|
|
|
-- Step 3: Drop the old single push_token column
|
|
ALTER TABLE users DROP COLUMN IF EXISTS push_token;
|
|
|
|
-- Step 4: Add index for better performance on push_tokens
|
|
CREATE INDEX IF NOT EXISTS idx_users_push_tokens ON users USING GIN (push_tokens)
|
|
WHERE
|
|
push_tokens IS NOT NULL
|
|
AND push_tokens != 'null'::jsonb
|
|
AND jsonb_array_length(push_tokens) > 0
|
|
AND deleted_at IS NULL;
|
|
|
|
-- Step 5: Add comment for documentation
|
|
COMMENT ON COLUMN users.push_tokens IS 'JSON array of Najva push notification tokens from different user devices'; |