Padagomez Padagomez - 5 months ago 55
SQL Question

ERROR: failed to find conversion function from unknown to text

There is an error on PostgreSQL that it gives on one of my select statements. I searched the web for an answer and came out empty handed. The answer given in another question did not suit my problem.


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



My query looks something like this:

Select *
from (select 'string' as Rowname, Data
From table)
Union all
(select 'string2' as Rowname, Data
From table)


The point of doing this is to specify what the row is at one point. The string being the name of the row. Here is my desired output:

Rowname Data
string 53
string2 87


Any possible way to fix this error?

Answer

Your statement has a couple of problems. But the error message implies that you need an explicit cast to declare the (yet unknown) data type of the string literal 'string':

SELECT 'string'::text AS rowname, data FROM tbl1

UNION ALL
SELECT 'string2', data FROM tbl2

This is only required for the first SELECT of a UNION query. Later legs already know name and type for each column.

In other contexts (like INSERT) Postgres can derive the data type from the target columns and tries to coerce to the right data type automatically.