Search code examples
sqldatabasepostgresqltype-conversionunion

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?

Solution

  • Update: Type resolution in later versions of Postgres became smarter and this rule for UNION, CASE, and Related Constructs resolves it to text without explicit cast:

    1. If all inputs are of type unknown, resolve as type text (the preferred type of the string category). [...]
    SELECT 'string' AS rowname, data FROM tbl1
UNION ALL
SELECT 'string2', data FROM tbl2;

    In older versions before Postgres 9.4 (?), or for non-default types you may still need to add an explicit cast like below.

    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.