PostgreSQL convert anyarray to array of type int

Question

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.

Hm, postgresql.org/docs/current/datatype-pseudo.html says "A pseudo-type cannot be used as a column data type", but pg_stats doesn't seem to care — Bergi
– Bergi, Commented Jan 19, 2022 at 3:10
Try histogram_bounds::text? I have a feeling that always works. Maybe then cast that text to int[] — Bergi
– Bergi, Commented Jan 19, 2022 at 3:14
@Bergi Thank you! This is the answer - CAST(histogram_bounds::text AS int[]), or histogram_bounds::text::int[], works. If you answer the question I can mark it as answer. — Aserian
– Aserian, Commented Jan 20, 2022 at 3:06

Bergi · Accepted Answer · 2022-01-20 08:20:26Z

4

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>';

edited Jan 20, 2022 at 8:20

answered Jan 20, 2022 at 8:15

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL convert anyarray to array of type int

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related