Thread: pg_attribute.atttypmod for interval type
Hi,
I work on a driver for Postgres that utilizes the extended query protocol and have a question about the row description message for interval types.
I am trying to use the type modifier value from the message to determine the precision of the interval. This is something I can do for timestamp types because the type modifier in the message is equal to the precision (or -1 if not specified).
For interval types it seems like the type modifier is related to the precision but the values are not equal. Using Postgres 13.10 I see the following values for pg_attribute.attttypmod when creating columns of type interval(1), interval(2), ..., interval(6)
2147418113
2147418114
2147418115
2147418116
2147418117
2147418118
I can see the value goes up by 1 each time the precision is increased, but I'm not sure how to interpret the fact that it starts at 2147418113 instead of 1.
My question is: how are the values meant to be interpreted for interval types? Thanks very much for your help.
Greg Rychlewski <greg.rychlewski@gmail.com> writes: > My question is: how are the values meant to be interpreted for interval > types? Thanks very much for your help. Interval typmods include a fractional-seconds-precision field as well as a bitmask indicating the allowed interval fields (per the SQL standard's weird syntax such as INTERVAL DAY TO SECOND). Looking at the source code for intervaltypmodout() might be helpful: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/timestamp.c;h=69fe7860ede062fc8be42e7545b35e69c3e068c4;hb=HEAD#l1136 The referenced macros are mostly in utils/timestamp.h and utils/datetime.h. regards, tom lane
On 07/27/24 00:32, Tom Lane wrote: > Interval typmods include a fractional-seconds-precision field as well > as a bitmask indicating the allowed interval fields (per the SQL > standard's weird syntax such as INTERVAL DAY TO SECOND). Looking at > the source code for intervaltypmodout() might be helpful: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/timestamp.c;h=69fe7860ede062fc8be42e7545b35e69c3e068c4;hb=HEAD#l1136 Also, for this kind of question, an overview of a type modifier's contents can be found in the javadoc for the WIP PL/Java 1.7, which is intended to model such things accurately.[0] The model is aimed at the logical level, that is, to represent what information is present in the typmod, the precise semantics, what combinations are allowed/disallowed, and so on, but not the way PostgreSQL physically packs the bits. So, for this case, what you would find there is essentially what Tom already said, about what's logically present; it doesn't save you the effort of looking in the PostgreSQL source if you want to independently implement unpacking the bits. For possible future typmod questions, it may serve as a quick way to get that kind of logical-level description at moments when Tom is away from the keyboard. Regards, -Chap [0] https://tada.github.io/pljava/preview1.7/pljava-api/apidocs/org.postgresql.pljava/org/postgresql/pljava/adt/Timespan.Interval.Modifier.html I just noticed a nit in that javadoc: it says the field combination must be "one of the named constants in this interface" but you don't find them in the Interval.Modifier interface; they're in the containing interface Interval itself.