ERROR: failed to find conversion function from unknown to text

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

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.