Postgresql error: "failed to find conversion function from unknown to text"

Question

I have the following postgresql query:

   with A as 
   (
          select '201405MASE04' as TestID, Count(*) TotQ, Count(distinct Case When SE1 = '' then NULL else SE1 end) TotSE, 
                 case when Count(*)=0 then 7 else Count(distinct RC) end TotRC
          from eivTestItems TI, eivTests T 
          where TI.Test_ID = T.Test_ID 
                 and T.Test_Type_ID = 1
                 and T.Test_ID=  '201405MASE04'
                 and TI.Omit <> 1
   ), 
   B as 
   (
          select '201405MASE04' as TestID, Count(*) TotQ
          from eivTestItems TI, eivTests T 
          where TI.Test_ID = T.Test_ID 
                 and T.Test_Type_ID = 1
                 and T.Test_ID=  '201405MASE04'
                 and TI.Omit = 1
   ),
   C as
   (
          select '201405MASE04' as TestID, Count(*) TotQ
          from eivTestItems TI, eivTests T 
          where TI.Test_ID = T.Test_ID 
                 and T.Test_Type_ID = 1
                 and T.Test_ID=  '201405MASE04'
                 and TI.Question_Type_ID='2'
   )

   Select A.TestID, A.TotQ + coalesce(B.TotQ,0) - coalesce(C.TotQ,0) as TotQ, A.TotSE, A.TotRC
   From A
   left outer Join B on A.TestID = B.TestID
   left outer Join C on A.TestID = C.TestID

When i am trying to run this query, it is throwing me the following error:

ERROR:  failed to find conversion function from unknown to text

********** Error **********

ERROR: failed to find conversion function from unknown to text
SQL state: XX000

How do i find out where am i getting the conversion error here?

Answer 1

It looks like postgres doesn't like your constant '201405MASE04'.

SQL Fiddle Demo that generates the same error you get.

SQL Fiddle Demo showing the cast the datatype fixes the issue.

Try this. I define it as text

with A as 
   (
          select cast('201405MASE04' as text) as TestID, Count(*) TotQ, Count(distinct Case When SE1 = '' then NULL else SE1 end) TotSE, 
                 case when Count(*)=0 then 7 else Count(distinct RC) end TotRC
          from eivTestItems TI, eivTests T 
          where TI.Test_ID = T.Test_ID 
                 and T.Test_Type_ID = 1
                 and T.Test_ID=  '201405MASE04'
                 and TI.Omit <> 1
   ), 
   B as 
   (
          select cast('201405MASE04' as text) as TestID, Count(*) TotQ
          from eivTestItems TI, eivTests T 
          where TI.Test_ID = T.Test_ID 
                 and T.Test_Type_ID = 1
                 and T.Test_ID=  '201405MASE04'
                 and TI.Omit = 1
   ),
   C as
   (
          select cast('201405MASE04' as text) as TestID, Count(*) TotQ
          from eivTestItems TI, eivTests T 
          where TI.Test_ID = T.Test_ID 
                 and T.Test_Type_ID = 1
                 and T.Test_ID=  '201405MASE04'
                 and TI.Question_Type_ID='2'
   )

   Select A.TestID, A.TotQ + coalesce(B.TotQ,0) - coalesce(C.TotQ,0) as TotQ, A.TotSE, A.TotRC
   From A
   left outer Join B on A.TestID = B.TestID
   left outer Join C on A.TestID = C.TestID