postgresqlcastingintegerintervals

casting (converting) integer into interval in PostgreSQL

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:

  1. SELECT '10'::interval day;
  2. 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.

Solution

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