PostgreSQL convert anyarray to array of type int-CodePudding

I am trying to work with the pg_stats relation to learn more about how PostgreSQL does query evaluation.

I am trying to follow this page here: https://www.postgresql.org/docs/10/row-estimation-examples.html to calculate some of the selectivity on my queries.

When working with the histogram_bounds as obtained from

SELECT histogram_bounds 
FROM pg_stats 
WHERE tablename='<table_name>' 
  AND attname='<att_name>';

where att_name here is an attribute of numerical type in the table table_name

The returned value is of type anyarray, even though (in my case) the attribute is an integer, so it's an anyarray full of integers. I have found very little documentation on this type, but it seems I can't use the simple cast conventions for getting it to a usable type. Apparently it does not support normal array options. I would like to cast this to an array of ints.

histogram_bounds[0]

ERROR: cannot subscript type anyarray because it is not an array

And cannot be cast

CAST(histogram_bounds as int[])

ERROR: cannot cast type anyarray to integer[]

Any help with how to cast this type to an int array would be very much appreciated.

CodePudding user response：

I think you can cast any type, even the anyarray one, to text for output. This text will have the integer array representation for a histogram of an integer column, so we can subsequently cast it to that:

SELECT histogram_bounds::text::int[]
FROM pg_stats
WHERE tablename='<table_name>'
  AND attname='<att_name>';