英文:
How to insert data into a table with UUIDs in a string column using a PostgreSQL procedure?
问题
我的任务是将数据插入到现有表中。
我的表名为 user_entity。列如下：
id (主键) 36 例子 - "a0a16d3f-ad38-491d-a98d-5af80428e139"
email
email_constraint
email_verified
enabled (true/false)
first_name
last_name
realm_id
username
created_timestamp
not_before
我想编写一个循环来将不同的数据插入到表中。
下面是我错误的过程。
我想生成唯一的 id 和其他数据，例如 Roman001、Chovgun001、email，并且该值应该递增 (+1)。但是我不知道如何做，因为这些列不是整数。
我哪里出错了？是否可能实现？
CREATE PROCEDURE create_cs_kc_users()
LANGUAGE plpgsql
AS $procedure$
BEGIN
DECLARE
usersTotalCount := 100;
n := 0;
while n <= usersTotalCount loop
insert into edu_power_kc.user_entity (id, email, email_constraint, email_verified, enabled, first_name, last_name, realm_id, username, created_timestamp, not_before)
values (a0a16d3f-ad38-491d-a98d-5af80428e139, roman001@gmail.com, roman001@gmail.com, false, true, Roman001, Chovgun001, EduPowerKeycloak, romanchovgun001, 1623746793274, 0)
n := n + 1;
END
$procedure$
;
英文:
My task is to insert data into an existing table.
My table is called user_entity. Columns:
id (PK) 36 example - "a0a16d3f-ad38-491d-a98d-5af80428e139"
email
email_constraint
email_verified
enabled (true/false)
first_name
last_name
realm_id
username
created_timestamp
not_before
I want to write a loop to insert different data into the table.
Below you can see my bad procedure.
I want to generate unique id and other data, e.g. Roman001, Chovgun001, email, and the value should increase (+1). But I don't know how to do it, because these columns are not integer.
Where did I make mistakes? Is it possible to do?
CREATE PROCEDURE create_cs_kc_users()
LANGUAGE plpgsql
AS $procedure$
BEGIN
DECLARE
usersTotalCount := 100;
n := 0;
while n <= usersTotalCount loop
insert into edu_power_kc.user_entity (id, email, email_constraint, email_verified, enabled, first_name, last_name, realm_id, username, created_timestamp, not_before)
values (a0a16d3f-ad38-491d-a98d-5af80428e139, roman001@gmail.com, roman001@gmail.com, false, true, Roman001, Chovgun001, EduPowerKeycloak, romanchovgun001, 1623746793274, 0)
n := n + 1;
END
$procedure$
;
答案1
得分: 1
你的主键似乎是字符数据类型（而不是uuid，这将是正确的数据类型），因此您可以使用gen_random_uuid()函数生成值，并将结果类型转换为text：
INSERT INTO edu_power_kc.user_entity (id, ...)
VALUES (CAST(gen_random_uuid() AS text), ...);
请注意，字符串常量必须用单引号括起来，如'roman001@gmail.com'。
英文:
Your primary key seems to be a character data type (rather than uuid, which would be the proper data type), so you could generate values with the gen_random_uuid() function and type cast the result to text:
INSERT INTO edu_power_kc.user_entity (id, ...)
VALUES (CAST(gen_random_uuid() AS text), ...);
Note that string constants have to be surrounded by single quotes, like 'roman001@gmail.com'.
答案2
得分: 0
以下是您要的翻译部分：
-
It works with next code
这与下面的代码一起工作 -
Thanks a lot for help!
非常感谢您的帮助！ -
CREATE OR REPLACE PROCEDURE create_cs_users(usersCount integer) LANGUAGE plpgsql AS $procedure1$ DECLARE
创建或替换过程create_cs_users（用户数整数）LANGUAGE plpgsql AS $procedure1$ DECLARE -
userIndex integer := 1;
userIndex 整数 := 1; -
userUuid text; BEGIN
userUuid 文本; 开始 -
WHILE userIndex <= usersCount LOOP
WHILE userIndex <= usersCount LOOP -
userUuid = gen_random_uuid();
userUuid = gen_random_uuid(); -
insert into edu_power_kc.user_entity (id, email, email_constraint, email_verified, enabled, first_name, last_name, realm_id, username, created_timestamp, not_before)
插入到edu_power_kc.user_entity（id，电子邮件，电子邮件约束，电子邮件已验证，已启用，名字，姓氏，领域ID，用户名，创建时间戳，不在之前） -
values (CAST(userUuid AS text), 'mail'||userIndex||'@gmail.com', 'mail'||userIndex||'@gmail.com', false, true, 'csname'||userIndex, 'cslastname'||userIndex, 'EduPowerKeycloak', 'cslogin'||userIndex, 1623746793274, 0);
值（将userUuid转换为文本，'mail'||userIndex||'@gmail.com'，'mail'||userIndex||'@gmail.com'，false，true，'csname'||userIndex，'cslastname'||userIndex，'EduPowerKeycloak'，'cslogin'||userIndex，1623746793274，0） -
CREATE OR REPLACE PROCEDURE put_users_attribute(userUuid text, userIndex integer)
创建或替换过程put_users_attribute（userUuid文本，userIndex整数） -
LANGUAGE plpgsql
LANGUAGE plpgsql -
AS $procedure2$
AS $procedure2$ -
BEGIN
开始 -
insert into edu_power_kc.user_attribute (name, value, user_id, id) VALUES
插入到edu_power_kc.user_attribute（名称，值，用户ID，ID）VALUES -
('locale', 'en', userUuid, CAST(gen_random_uuid() AS text)),
（'locale'，'en'，userUuid，将gen_random_uuid()转换为文本的CAST） -
('mobile_number_verified', 'true', userUuid, CAST(gen_random_uuid() AS text)),
（'mobile_number_verified'，'true'，userUuid，将gen_random_uuid()转换为文本的CAST） -
('sms_auth.failed_tries', 0, userUuid, CAST(gen_random_uuid() AS text)),
（'sms_auth.failed_tries'，0，userUuid，将gen_random_uuid()转换为文本的CAST） -
('installation_zone_id', 0, userUuid, CAST(gen_random_uuid() AS text)),
（'installation_zone_id'，0，userUuid，将gen_random_uuid()转换为文本的CAST） -
('mobile_number', 999999999, userUuid, CAST(gen_random_uuid() AS text)),
（'mobile_number'，999999999，userUuid，将gen_random_uuid()转换为文本的CAST） -
('middleName', 'csmiddlename'||userIndex, userUuid, CAST(gen_random_uuid() AS text)),
（'middleName'，'csmiddlename'||userIndex，userUuid，将gen_random_uuid()转换为文本的CAST） -
('avatar-url', '/download/noavatar.png', userUuid, CAST(gen_random_uuid() AS text));
（'avatar-url'，'/download/noavatar.png'，userUuid，将gen_random_uuid()转换为文本的CAST） -
END
END -
$procedure2$
$procedure2$ -
;
; -
CALL put_users_attribute(userUuid, userIndex);
调用put_users_attribute（userUuid，userIndex） -
userIndex = userIndex + 1;
userIndex = userIndex + 1; -
END LOOP; END $procedure1$ ;
END LOOP; END $procedure1$；
英文:
It works with next code
Thanks a lot for help!
CREATE OR REPLACE PROCEDURE create_cs_users(usersCount integer) LANGUAGE plpgsql AS $procedure1$ DECLARE
userIndex integer := 1;
userUuid text; BEGIN
WHILE userIndex <= usersCount LOOP
userUuid = gen_random_uuid();
insert into edu_power_kc.user_entity (id, email, email_constraint, email_verified, enabled, first_name, last_name, realm_id, username, created_timestamp, not_before)
values (CAST(userUuid AS text), 'mail'||userIndex||'@gmail.com', 'mail'||userIndex||'@gmail.com', false, true, 'csname'||userIndex, 'cslastname'||userIndex, 'EduPowerKeycloak', 'cslogin'||userIndex, 1623746793274, 0);
CREATE OR REPLACE PROCEDURE put_users_attribute(userUuid text, userIndex integer)
LANGUAGE plpgsql
AS $procedure2$
BEGIN
insert into edu_power_kc.user_attribute (name, value, user_id, id) VALUES
('locale', 'en', userUuid, CAST(gen_random_uuid() AS text)),
('mobile_number_verified', 'true', userUuid, CAST(gen_random_uuid() AS text)),
('sms_auth.failed_tries', 0, userUuid, CAST(gen_random_uuid() AS text)),
('installation_zone_id', 0, userUuid, CAST(gen_random_uuid() AS text)),
('mobile_number', 999999999, userUuid, CAST(gen_random_uuid() AS text)),
('middleName', 'csmiddlename'||userIndex, userUuid, CAST(gen_random_uuid() AS text)),
('avatar-url', '/download/noavatar.png', userUuid, CAST(gen_random_uuid() AS text));
END
$procedure2$
;
CALL put_users_attribute(userUuid, userIndex);
userIndex = userIndex + 1;
END LOOP; END $procedure1$ ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论