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?
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 text 'string' AS rowname, data FROM tbl1
UNION ALL
SELECT 'string2', data FROM tbl2
It's enough to cast in one SELECT
of a UNION
query. Typically the first one, where column names are also decided. Subsequent SELECT
lists with unknown types will fall in line.
In other contexts (like the VALUES
clause attached to an INSERT
) Postgres derives data types from target columns and tries to coerce to the right type automatically.