Hi Igal:
On Mon, 1 Jul 2024 at 01:17, Igal Sapir <igal@lucee.org> wrote:
> I actually did test the expression that I posted, but it might be casting it twice. While your examples that you
wroteshow 1 month correctly:
> SELECT (interval '1 ' || 'month');
> ?column? |
> -------------+
> 00:00:01month|
No, it does not, try it like this:
s=> with a(x) as ( SELECT (interval '1 ' || 'month')) select x,
pg_typeof(x) from a;
x | pg_typeof
---------------+-----------
00:00:01month | text
(1 row)
And you'll understand what is happening. Cast to interval has higher
priority then concatenation, so you are selecting a 1 second interval,
casting it to text, '00:00:01', adding 'month' at end.
This can also be noticed because month output would not use ':' and have spaces:
s=> with a(x) as ( SELECT '001.00MONTHS'::interval) select x,
pg_typeof(x) from a;
x | pg_typeof
-------+-----------
1 mon | interval
(1 row)
( I used fractions, uppercase and no spaces on input to show how
interval output normalizes ).
Francisco Olarte.