0

I am trying to insert user and multiple of his user role + return id of created user in single row. I used following query:

WITH inserted_user as (
 insert into "user" (username, password, nickname, account_non_expired, 
account_non_locked, credentials_non_expired, enabled)
  values ('username', 'password-hash', 'nickanme', true, true, true, true)
  returning id
)
insert into user_role values
  ((select id
    from inserted_user), 2),
  ((select id
    from inserted_user), 3),
  ((select id
    from inserted_user), 4)
returning user_id;

My issue is, that in my result I see 3 rows with the same ID (this is bad for my java application which expects 1 number). I am not sure how to edit this to get my result. I tried to wrap this into another select + many other more or less dumb things, but none of it was working. Could you please help me?

1
  • yes, user_id from user_role table (it is first parameter I insert) or id from user table or id from inserted_user, all of this is exactly the same value
    – Eleer
    Jul 9 '18 at 9:41
2

Use another CTE for the second INSERT and SELECT DISTINCT user_id FROM that CTE. In the new CTE do an INSERT ... SELECT FROM the old CTE.

WITH inserted_user
AS
(
INSERT INTO "user"
            (username,
             password,
             nickname,
             account_non_expired, 
             account_non_locked,
             credentials_non_expired,
             enabled)
            VALUES ('username',
                    'password-hash',
                    'nickanme',
                    true,
                    true,
                    true,
                    true)
            RETURNING id
),
inserted_user_role
AS
(
INSERT INTO user_role
            SELECT iu.id,
                   gs.n
                   FROM inserted_user iu
                        CROSS JOIN generate_series(2, 4) gs(n)
            RETURNING user_id
)
SELECT DISTINCT
       user_id
       FROM inserted_user_role;
1
  • Yes. the final select can also be SELECT id FROM inserted_user; Jul 9 '18 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.