PostgreSQL Question posted in
The official documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    The first example is an assignment. '10' is an unknown-type constant. When you assign interval type to it, Postgres assumes it’s '10 seconds':

    explain verbose SELECT '10'::interval;
    Output: ’00:00:10′::interval

    The day is normally only a field restriction type modifier but in an assignment context, it also changes the assumption about units and explain can show you that it’s altering the constant to '10 days':

    explain verbose SELECT '10'::interval day;
    Output: ’10 days’::interval day

    Unfortunately, the doc doesn’t describe the second part of that mechanism:

    The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases:

    YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

    The reason it does that is likely the principle of least astonishment – it’s not unreasonable to expect 10 days when you type interval day '10' or '10'::interval day.

    SELECT 10::text::interval day;

    The second example involves one assignment and one cast. That whole guesswork that applies in the assignment context, still kicks in, but now it sees a 10 that wants to become text, so no additional steps are taken: (10::unknown)::text → '10'::text

    On the second cast of what’s already a textual '10' to an interval, the day restriction does not affect the constant – it’s been pre-processed already and at that stage no further guesswork trickery takes place. As a result it becomes a 00:00:10 which is then subjected to the day restriction, so to only keep whole days, the 10 seconds get truncated away, leaving you with 00:00:00.

    All constants start off as ::unknown but only the first type assignment from that is an assignment where this sort of thing can happen, the rest is a cast that works differently. Unfortunately, you can’t go back and re-do the assignment:

    select '10'::unknown--a no-op, it's implied
           ::text   --an assignment, because it's the first known type
           ::unknown--does not go back to unassigned unknown, just a weird cast
                    --allowed pretty much only if the re-writer can reduce it
           ::interval day--won't work now in the cast context from unknown
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search