92

In one of my select statements I've got the following error:

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

This was easy to fix using cast, but I do not fully understand why it happened. I will ilustrate my confusion with two simple statements.

This one is OK:

select 'text'
union all
select 'text';

This will return error:

with t as (select 'text')    
select * from t
union all
select 'text'

I know I can fix it easily:

with t as (select 'text'::text)    
select * from t
union all
select 'text'

Why does the conversion fail in the second example? Is there some logic I do not understand or this will be fixed in future version of PostgreSQL?

PostgreSQL 9.1.9

The same behavior on PostgreSQL 9.2.4 (SQL Fiddle)

Beryllium
  • 12,808
  • 10
  • 56
  • 86
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
  • 2
    `::text` didn't work for me. This solution did, putting 'text' before the text literal: https://stackoverflow.com/a/25193282/349169 – Chris May 12 '20 at 10:55
  • 1
    Using PostgreSQL 9.6 throws the exception, using PostgreSQL 10 and above works though... (checked with https://www.db-fiddle.com) – kaaas Sep 01 '20 at 15:08
  • if `::text` doesn't work you can also try `cast('my text' as text)` – Vivek Ramanathan May 24 '22 at 21:52

1 Answers1

81

Postgres is happy, if it can detect types of untyped constants from the context. But when any context is not possible, and when query is little bit more complex than trivial, then this mechanism fails. These rules are specific for any SELECT clause, and some are stricter, some not. If I can say, then older routines are more tolerant (due higher compatibility with Oracle and less negative impact on beginners), modern are less tolerant (due higher safety to type errors).

There was some proposals try to work with any unknown literal constant like text constant, but was rejected for more reasons. So I don't expect significant changes in this area. This issue is usually related to synthetic tests - and less to real queries, where types are deduced from column types.

Beryllium
  • 12,808
  • 10
  • 56
  • 86
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • 6
    I see. I face this issue quite frequently in real life. We are using PostgreSQL for analytics/bi/data mining and union of untyped constants is common. But as I said, it is easy to cast. – Tomas Greif Aug 07 '13 at 06:08
  • 3
    Why don't I have to cast numeric literals like `1::int`? – Iain Samuel McLean Elder May 06 '14 at 12:40
  • 1
    @IainElder - numeric types are smaller class - any literal constant (named as "unknown") can be implicitly cast to any type - number not - so sin('2.34') is working, but length(1) not – Pavel Stehule May 07 '14 at 05:47
  • 7
    I keep coming back to this question while trying to `union` results. And future me is lazy so I don't read the entire question, which actually includes the answer: convert the union'd values to text. – samthebrand Jun 06 '18 at 20:06