1

I am doing a migration from MSSQL to PostgreSQL

MSSQL query:

SELECT * FROM dbo.Mtr_userdetails AS table1 JOIN( SELECT urmo.urm_userid STUFF((SELECT ',' + urm.urm_role_name FROM dbo.STL_CS_Mtr_userrolemapping urm WHERE urm.urm_userid = 'test2' AND urm.urm_status = 'A' AND urm.urm_appid = 'BCA' FOR XML PATH('')),1,1,'') [user_roles],urmo.urm_appid FROM dbo.Mtr_userrolemapping urmo WHERE urmo.urm_appid = 'BCA' AND urmo.urm_userid = 'test2' GROUP BY urmo.urm_userid,urmo.urm_appid) AS table2 ON table1.ud_userid = table2.urm_userid WHERE (table1.ud_userid = 'test2')

I am trying to convert the above ms sql query to postgresql syntax.

PostgreSQL query:

SELECT *
FROM STL_Mtr_userdetails AS table1
  JOIN (
    SELECT urmo.urm_userid,
           string_agg((SELECT ',' || urm.urm_role_name 
                       FROM STL_CS_Mtr_userrolemapping urm
                       WHERE urm.urm_userid = 'test2' 
                         AND urm.urm_status = 'A' 
                         AND urm.urm_appid = 'BCA')::varchar, 1::varchar, 1::varchar, ''::varchar) user_roles,
           urmo.urm_appid
    FROM STL_CS_Mtr_userrolemapping urmo
    WHERE urmo.urm_appid = 'BCA'
      AND urmo.urm_userid = 'test2'
    GROUP BY urmo.urm_userid,
             urmo.urm_appid
  ) AS table2 ON table1.ud_userid = table2.urm_userid
WHERE (table1.ud_userid = 'test2')

I am facing the below error when executing my postgresql query

string_agg(character varying,character varying,character varying,character varying) does not exist. No hint matches the given name and argument types. you might need to add explicit type casts.

Rlaks
  • 7
  • 1
  • 5
  • 2
    I *really* recommend using whitespace and linebreaks when writing (I don't just mean writing code, I mean in general). That code is *far* too wide for a "one liner". – Thom A Feb 05 '21 at 14:54
  • Well, [as documented in the manual](https://www.postgresql.org/docs/current/functions-aggregate.html) string_agg() only takes two parameters, not 4 –  Feb 05 '21 at 14:58
  • Sure, will follow that – Rlaks Feb 09 '21 at 05:42

1 Answers1

0

There is no need for a complicated nested scalar sub-select. Just aggregate directly:

SELECT *
FROM STL_Mtr_userdetails AS table1
  JOIN (
    SELECT urmo.urm_userid,
           string_agg(urm.urm_role_name, ',') as user_roles
    FROM STL_CS_Mtr_userrolemapping urmo
    WHERE urmo.urm_appid = 'BCA'
    GROUP BY urmo.urm_userid
  ) AS table2 ON table1.ud_userid = table2.urm_userid
WHERE table1.ud_userid = 'test2'

The error message stems from the fact that you are trying to call a string_agg() function with four (varchar) parameters. But string_agg() is defined to receive only two parameters (the values to aggregate and the delimiter).

  • Thank you for your answer. I executed the above query and it says missing FROM-clause entry for table 'urm' – Rlaks Feb 08 '21 at 04:48