manipulating anyarray columns

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: manipulating anyarray columns
Date: 2012-05-05 16:04:04
Message-ID: 4FA54F74.8070604@fuzzy.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Tzeggai 2012-05-05 21:19:14 Lost one tablespace - can't access whole database
Previous Message Tulio 2012-05-05 02:22:20 Re: spanish locale question