Follow up to this question.
Say that on postgres, I have a table TABLE1 containing the columns id (integer), name (string):
create table table1 (id int primary key,name varchar(100)); insert into table1(id,name) values(5,'a'); insert into table1(id,name) values(6,'b'); insert into table1(id,name) values(7,'c'); insert into table1(id,name) values(55,'a');
And attempt to run the following queries:
with base (x) as (select 5 as x from table1) select table1.name from base, table1 where table1.id = base.x; with base (x) as (select 'a' as x from table1) select table1.name from base, table1 where table1.name = base.x;
On sqlfiddle, the former yields a result, whilst the latter fails with the message:
ERROR: failed to find conversion function from unknown to text
On postgres 13.3 which I have installed locally, however, neither errs. (Nor similar queries on oracle and sqlite.)
My first question is, does this error stem from an issue within sqlfiddle, or has it persisted within earlier versions of postgres?
And second, does this count as a bug? Generally, are constant columns (or values) in SQL assumed to be typeless, and any operations on them are undefined unless there happens to be an implicit / explicit cast in place?
Per my understanding, using constant columns for joining is generally inadvisable as it thwarts indexing, but it seems a little odd in any programming language to have difficulties telling one constant format apart from another.
Advertisement
Answer
The cause is that a string literal in PostgreSQL is of type unknown, not text, because all data types have a string representation. Normally, the actual type is determined by context.
A number literal, however, has data type integer, bigint or numeric, based on its size and the presence of a fractional part:
SELECT pg_typeof('a');
pg_typeof
═══════════
unknown
(1 row)
SELECT pg_typeof(5);
pg_typeof
═══════════
integer
(1 row)
Now the subquery select 'a' as x from table1 has no context to determine a better type than unknown for the result column, which makes the comparison in the join condition fail.
Since this is strange and undesirable behavior, PostgreSQL v10 has demoted unknown from being a regular data type (you could create columns of that type!) to a “pseudo-type”. It also coerced unknown to text in SELECT lists, which is why you cannot reproduce that on v10 or later.