If I execute query: SELECT '10' AS a_name;
it gives 10 AS text type.
In case of query: SELECT 10::text AS a_name;
it gives again 10 as text.
So one could expect that following two queries gave the same result:
SELECT '10'::interval day;
SELECT 10::text::interval day;
Nevertheless the first query gives 10 days
and the second gives 00:00:00
. both type of interval.
Please, explain why SELECT '10'
and SELECT 10::text
are interpreted in different way even they both give the same tape and values, or where is my understanding mistaken.
The difference in behavior is because the two expressions, '10'
and '10'::TEXT
are not semantically equivalent within the database. In the expression '10'::INTERVAL DAY
, '10'
is a symbol of unknown type which is then cast to INTERVAL DAY
. In the expression '10'::TEXT::INTERVAL DAY
, '10'
is again a symbol of unknown type, which is cast to TEXT
before being cast to INTERVAL DAY
. The reason '10'
shows in the client as having type TEXT
is because of an implicit cast from unknown type to TEXT
when the results are returned to the client.