How to insert data into a table with UUIDs in a string column using a PostgreSQL procedure?

huangapple
go评论87阅读模式
英文:

How to insert data into a table with UUIDs in a string column using a PostgreSQL procedure?

问题

我的任务是将数据插入到现有表中。

我的表名为 user_entity。列如下：

  1. id (主键) 36 例子 - "a0a16d3f-ad38-491d-a98d-5af80428e139"
  2. email
  3. email_constraint
  4. email_verified
  5. enabled (true/false)
  6. first_name
  7. last_name
  8. realm_id
  9. username
  10. created_timestamp
  11. not_before

我想编写一个循环来将不同的数据插入到表中。
下面是我错误的过程。

我想生成唯一的 id 和其他数据，例如 Roman001、Chovgun001、email，并且该值应该递增 (+1)。但是我不知道如何做，因为这些列不是整数。

我哪里出错了？是否可能实现？

  1. CREATE PROCEDURE create_cs_kc_users()
  2.     LANGUAGE plpgsql
  3. AS $procedure$
  4. BEGIN
  5. DECLARE
  6. usersTotalCount := 100;
  7. n := 0;
  8. while n <= usersTotalCount loop
  9. 	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)
  10. 	values (a0a16d3f-ad38-491d-a98d-5af80428e139, roman001@gmail.com, roman001@gmail.com, false, true, Roman001, Chovgun001, EduPowerKeycloak, romanchovgun001, 1623746793274, 0)
  11. 	n := n + 1;
  12. END
  13. $procedure$
  14. ;
英文:

My task is to insert data into an existing table.

My table is called user_entity. Columns:

  1. id (PK) 36 example - "a0a16d3f-ad38-491d-a98d-5af80428e139"
  2. email
  3. email_constraint
  4. email_verified
  5. enabled (true/false)
  6. first_name
  7. last_name
  8. realm_id
  9. username
  10. created_timestamp
  11. 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?

  1. CREATE PROCEDURE create_cs_kc_users()
  2.     LANGUAGE plpgsql
  3. AS $procedure$
  4. BEGIN
  5. DECLARE
  6. usersTotalCount := 100;
  7. n := 0;
  8. while n <= usersTotalCount loop
  9. 	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)
  10. 	values (a0a16d3f-ad38-491d-a98d-5af80428e139, roman001@gmail.com, roman001@gmail.com, false, true, Roman001, Chovgun001, EduPowerKeycloak, romanchovgun001, 1623746793274, 0)
  11. 	n := n + 1;
  12. END
  13. $procedure$
  14. ;

答案1

得分: 1

你的主键似乎是字符数据类型（而不是uuid，这将是正确的数据类型），因此您可以使用gen_random_uuid()函数生成值，并将结果类型转换为text：

  1. INSERT INTO edu_power_kc.user_entity (id, ...)
  2.    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:

  1. INSERT INTO edu_power_kc.user_entity (id, ...)
  2.    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!

  1. CREATE OR REPLACE PROCEDURE create_cs_users(usersCount integer) LANGUAGE plpgsql AS $procedure1$ DECLARE
  2.     userIndex integer := 1;
  3.     userUuid text; BEGIN
  4.     WHILE userIndex &lt;= usersCount LOOP
  5.         userUuid = gen_random_uuid();
  6.         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)
  7.         values (CAST(userUuid AS text), &#39;mail&#39;||userIndex||&#39;@gmail.com&#39;, &#39;mail&#39;||userIndex||&#39;@gmail.com&#39;, false, true, &#39;csname&#39;||userIndex, &#39;cslastname&#39;||userIndex, &#39;EduPowerKeycloak&#39;, &#39;cslogin&#39;||userIndex, 1623746793274, 0);
  8.            CREATE OR REPLACE PROCEDURE put_users_attribute(userUuid text, userIndex integer)
  9.         LANGUAGE plpgsql
  10.         AS $procedure2$
  11.            BEGIN
  12.             insert into edu_power_kc.user_attribute (name, value, user_id, id) VALUES
  13.                (&#39;locale&#39;, &#39;en&#39;, userUuid, CAST(gen_random_uuid() AS text)),
  14.             (&#39;mobile_number_verified&#39;, &#39;true&#39;, userUuid, CAST(gen_random_uuid() AS text)),
  15.             (&#39;sms_auth.failed_tries&#39;, 0, userUuid, CAST(gen_random_uuid() AS text)),
  16.             (&#39;installation_zone_id&#39;, 0, userUuid, CAST(gen_random_uuid() AS text)),
  17.             (&#39;mobile_number&#39;, 999999999, userUuid, CAST(gen_random_uuid() AS text)),
  18.             (&#39;middleName&#39;, &#39;csmiddlename&#39;||userIndex, userUuid, CAST(gen_random_uuid() AS text)),
  19.             (&#39;avatar-url&#39;, &#39;/download/noavatar.png&#39;, userUuid, CAST(gen_random_uuid() AS text));
  20.         END
  21.         $procedure2$
  22.         ;
  23.         CALL put_users_attribute(userUuid, userIndex);
  24.         userIndex = userIndex + 1;
  25.     END LOOP; END $procedure1$ ;

huangapple
  • 本文由 发表于 2023年7月11日 11:59:23
  • 转载请务必保留本文链接：https://go.coder-hub.com/76658628.html
  • postgresql
  • stored-procedures
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定