Hello!
When doing a SUM( ) aggregate on an INTERVAL HOUR TO SECOND(0) column, the resulting type loses the interval type qualifiers...
Obviously one can cast the SUM() to get the original type back, but I was wondering if there is a simpler way to handle this, to not force programmers to add a CAST() or :: all over the place.
The type of a SELECT item is important to us because we have generic C code that identifies the PQfmod() type, to properly interpret the string value returned by PQgetvalue().
With SUM(interval-column), PQfmod() returns -1 ...
Here some SQL sample to illustrate our concern by describing column types:
=======================================================================
create table tab1 ( pkey int, dur interval hour to second(0));
\d tab1
insert into tab1 values ( 101, interval '999:12:45');
insert into tab1 values ( 102, interval '100:00:00');
create view v1 as select sum(dur) from tab1;
select * from v1;
\d v1
create view v2 as select sum(dur)::interval hour to second(0) from tab1;
select * from v2;
\d v2
drop view v1;
drop view v2;
drop table tab1;
=======================================================================
Output:
CREATE TABLE
Table "public.tab1"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
pkey | integer | | |
dur | interval hour to second(0) | | |
INSERT 0 1
INSERT 0 1
CREATE VIEW
sum
------------
1099:12:45
(1 row)
View "public.v1"
Column | Type | Collation | Nullable | Default
--------+----------+-----------+----------+---------
sum | interval | | |
CREATE VIEW
sum
------------
1099:12:45
(1 row)
View "public.v2"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
sum | interval hour to second(0) | | |
DROP VIEW
DROP VIEW
DROP TABLE
Why is PostgreSQL "losing" the original type when doing aggregates like SUM()?
Seems the result can fit into an INTERVAL HOUR TO SECOND(0) since we can cast...
With other database engines like Informix, one defines a dimension for the largest interval qualifier:
INTERVAL HOUR(9) TO SECOND => can store 123456789:00:00 for ex
Is this is handled differently in PostgreSQL, right?
Sorry to ask if this is obvious to experts, but the documentation should be a bit more detailed for the interval type and its storage possibilities.
Seb
