Peter, thank you for clarification.
Could you please double check the following reasoning based on
https://www.postgresql.org/docs/current/btree-behavior.html?
- Index search by bigint column requires conversion of limiting
expressions to bigint type.
- Conversion from number(19,0) to bigint may cause overflow.
- So, index search is not possible.
On Tue, Jan 17, 2023 at 5:56 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Tue, Jan 17, 2023 at 5:44 PM Sam.Mesh <Sam.Mesh@gmail.com> wrote:
> > The problem arrises with the other case, where the values from the table are to be converted to match the datatype
ofthe query (i.e., we give numeric in query and the column is of bigint type). As Postgres can only cast a bigint to
numeric,the only option it has is to convert every row in the table to numeric and then compare. Thus in this case, the
indexwon’t be used.
> > ***
> >
> > Probably, somebody knows the current state of this limitation?
>
> Not all numeric values can be converted to int8 without loss of
> precision. If it was allowed, it would create subtle problems. The
> same is not true for (say) int4 and int8, which can be mixed in the
> way that you would expect.
>
> Internally, int4 and int8 are part of the same btree operator family,
> and so follow certain rules which are described here:
>
> https://www.postgresql.org/docs/current/btree-behavior.html
>
> --
> Peter Geoghegan