ERROR: failed to find conversion function from unknown to text

Question

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?

Bolden

postgresql doesn't usually need a FROM clause the way that Oracle (for example) does – Sarawak

Answer 1

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:

3. 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.

Outlook

Why is Postgresql wants us to strictly write the type? AFAIK, It should use the first query to determine the types for UNION statement and there is no other type in Postgresql that is defined as 'anythingOtherThanQuote'. Is it because Postgresql is not clever enough to infer the type or a design decision? – Sobranje

@burakemre: You seem to be missing that rowname is not derived from anything, but a genuinely new column. So the type cannot be inferred. – Outlook

I don't understand why Postgresql can't execute this query: "SELECT * FROM (SELECT 'string') a UNION ALL SELECT 'string2'", when I take out the UNION ALL part, it works as expected. Postgresql already knows about string literals and I expect Postgresql to use "text" type for the string literal 'string'. Could you please explain what am I missing here? – Sobranje

@burakemre: For the record: this has later been fixed and works without explicit cast now. – Outlook

Answer 2

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

Reference

Amish

Answer 3

Accessing the element of the anonymous row with .f2 got the same error as shown below:

postgres=# SELECT (ROW(2,'David',32)).f2;
ERROR:  failed to find conversion function from unknown to text

So, I set ::VARCHAR(20) just after .f2, then the error was solved as shown below:

postgres=# SELECT (ROW(2,'David',32)).f2::VARCHAR(20);
  f2
-------
 David
(1 row)

Brnaba