I am currently trying to do the following:
Here's the code:
class ConvertNameIntoFirstAndLastName<ActiveRecord::Migration
def up
execute<<- SQL
DO
$do$
DECLARE
record;
BEGIN
FOR u IN SELECT* FROM users LOOP
DECLARE
split_name text[];
BEGIN
split_name: = CASE WHEN u.name IS NULL THEEN '{'}'
ELSE regexp_split_to_array (u.name, E'\\s+')
END;
UPDATE users
SET
first_name = split_name[0],
last_name = CASE WHEN split_name[0] = split_name[-1]THEN'"
ELSE split_name [-1]
END,
name = split_name[0]|'|(CASE WHEN split_name[0]= split_name[-1]THEN'{'}'
ELSE split_name [-1]
END)
WHERE id=u.id;
END;
END LOOP;
END;
$do$;
SQL
end
def down
end
end
However, u.name in this SQL statement always returns null when the name is saved in Chinese characters.(DB encoding is set to Unicode.)
Below is the error.
PG::NotNullViolation:ERROR:null value in column "name" violates not-null constraint
=>Actually, the name contains a Chinese character string instead of null.
Has anyone solved the same problem?It would be helpful to know the cause and solution.
Thank you for your cooperation.
ruby-on-rails postgresql unicode
I'm sorry.Due to lack of study, the first value of Array is Array[0] in Postgresql.I didn't know that I couldn't get it without Array[1], or that there was no Array[-1].
The code below worked.
class ConvertNameIntoFirstAndLastName<ActiveRecord::Migration
def up
execute<<- SQL
DO
$do$
DECLARE
record;
BEGIN
FOR u IN SELECT* FROM users LOOP
DECLARE
split_name text[];
BEGIN
split_name: = regexp_split_to_array(regexp_replace(u.name, '', '), ');
UPDATE users
SET
first_name = split_name[1],
last_name = CASE WHEN split_name[1] = split_name [array_upper(split_name,1)]THEN'"
ELSE split_name [array_upper(splitted_name, 1)]
END,
name = split_name[1]|'|(CASE WHEN split_name[1]= split_name [array_upper(split_name,1)]THEN'"
ELSE split_name [array_upper(splitted_name, 1)]
END)
WHERE id=u.id;
END;
END LOOP;
END;
$do$;
SQL
end
def down
end
end
In the first place, Rails migrations are intended to change the schema (add tables and columns, change indexes, etc.), so I think it's a little out of place to change existing data (db: rollback is almost impossible).
Note: Active Record Migration | Rails Guide
Also, when I looked at the code, it didn't look like a process that I had to do on DB, so I thought it would be easier to write it on the Rails side.
For example, one way to do this is to have a conversion method available and run it on the rails console on the server.
class User<ActiveRecord::Base
def self.convert_name_into_first_and_last_name
self.transaction do
self.all.each do | user |
split_name =user.name.split(/[]+/)
user.first_name = split_name.first
# Separate consideration is required if split_name is divided into three or more parts
user.last_name=split_name.size==2?split_name.last:'"
user.name=splitted_name.join('')
user.save!
end
end
end
end
# rails console
User.convert_name_into_first_and_last_name
This will also make it easier to write test codes for the change process.
If you don't mind, please consider this kind of method.
© 2024 OneMinuteCode. All rights reserved.