Question 205937 in PostgreSQL Question posted in
The official documentation can be found here.

How to insert data into a table with UUIDs in a string column using a PostgreSQL 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, [email protected], [email protected], false, true, Roman001, Chovgun001, EduPowerKeycloak, romanchovgun001, 1623746793274, 0)
    n := n + 1;
END
$procedure$
;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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
    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$ ;

  2. 0

    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 '[email protected]'.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search