Thread: manipulating anyarray columns

Hi,

I have to deal with anyarray columns for the first time, and I've run
into some trouble. I need to read the pg_stats.most_common_vals column,
and read several items, i.e. most frequent values stored in the column.

With a regular column (as for example most_common_freqs) I can do this

  SELECT most_common_freqs[1:3]
  FROM pg_stats
  WHERE tablename = 'pg_attribute' AND attname = 'attname';

to get the first 3 items (frequencies), but with anyarray, I can't do
that - for example

  SELECT most_common_vals[1:3]
  FROM pg_stats
  WHERE tablename = 'pg_attribute' AND attname = 'attname';
  ERROR:  cannot subscript type anyarray because it is not an array

I do understand that many operations are not available with anyarray as
the actual type is unknown (it could be an array of arrays etc.) but why
is this impossible?

Surprisingly, I can do this:

  SELECT
    (string_to_array(array_to_string(most_common_vals,','),','))[1:3]
  FROM pg_stats
  WHERE tablename = 'pg_attribute' AND attname = 'attname';

which is good enough for me, but I'm curious why the simple approach
does not work. Or am I missing something / using it incorrectly?

Tomas

On 6.5.2012 16:41, Jov wrote:
> I find this work:
>
> SELECT (most_common_vals::varchar::varchar[])[1:3]
>  FROM pg_stats
>  WHERE tablename = 'pg_attribute' AND attname = 'attname';
>
> I try cast anyarray to varchar[] directly but failed,do'nt know why pg
> not support the cast.

IMHO this is exactly what I did, except that I casted the array to
string and back by calling functions (array_to_string/string_to_array)
and you did that by casting.

Tomas