59 lines
1.6 KiB
SQL
59 lines
1.6 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
|
|
-- =========================================
|
|
-- Разделение full_name на first_name и last_name
|
|
-- =========================================
|
|
|
|
-- Добавляем новые колонки
|
|
ALTER TABLE users
|
|
ADD COLUMN first_name VARCHAR(100),
|
|
ADD COLUMN last_name VARCHAR(100);
|
|
|
|
-- Копируем данные из full_name в новые поля
|
|
-- Разделяем по первому пробелу
|
|
UPDATE users
|
|
SET
|
|
first_name = CASE
|
|
WHEN position(' ' in full_name) > 0
|
|
THEN split_part(full_name, ' ', 1)
|
|
ELSE full_name
|
|
END,
|
|
last_name = CASE
|
|
WHEN position(' ' in full_name) > 0
|
|
THEN substring(full_name from position(' ' in full_name) + 1)
|
|
ELSE ''
|
|
END
|
|
WHERE full_name IS NOT NULL;
|
|
|
|
-- Делаем новые поля обязательными
|
|
ALTER TABLE users
|
|
ALTER COLUMN first_name SET NOT NULL,
|
|
ALTER COLUMN last_name SET NOT NULL;
|
|
|
|
-- Удаляем старую колонку
|
|
ALTER TABLE users DROP COLUMN full_name;
|
|
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
|
|
-- Восстанавливаем full_name
|
|
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
|
|
|
|
-- Объединяем имя и фамилию обратно
|
|
UPDATE users
|
|
SET full_name = first_name || ' ' || last_name
|
|
WHERE first_name IS NOT NULL AND last_name IS NOT NULL;
|
|
|
|
-- Делаем full_name обязательным
|
|
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
|
|
|
|
-- Удаляем новые колонки
|
|
ALTER TABLE users
|
|
DROP COLUMN first_name,
|
|
DROP COLUMN last_name;
|
|
|
|
-- +goose StatementEnd
|