Thread: could not find array type for data type character varying[]

Hi,

I'm trying to aggregate a list of table attributes into an array.

The actual code looks something like this:

  SELECT
    node_ref AS id,
    array_agg(DISTINCT ARRAY[namespace, name, value]) as annotations
  ...
  GROUP BY id;

I guess the minimal example that reproduces the error is:

  annis=> select array_agg(array['a'::varchar, 'b', 'c']);
  ERROR:  could not find array type for data type character varying[]

Why doesn't this work?

Cheers,
Viktor

Viktor Rosenfeld <listuser36@googlemail.com> writes:
>   annis=> select array_agg(array['a'::varchar, 'b', 'c']);
>   ERROR:  could not find array type for data type character varying[]

> Why doesn't this work?

The output of array_agg would have to be an array whose elements
are array-of-varchar.  Which is a datatype we don't have.
(2-D array is a different concept...)

            regards, tom lane

On Wed, Oct 28, 2009 at 04:17:32PM +0100, Viktor Rosenfeld wrote:
> I'm trying to aggregate a list of table attributes into an array.

I'd suggest using a tuple, arrays for things where each element means
the same thing.  I'd guess you care about the substructure (i.e. the
element has a "namespace", a "name" and a "value") and hence using an
array in the first place seems wrong.  Maybe something like:

  CREATE TYPE foo AS ( namespace TEXT, name TEXT, value TEXT );
  SELECT id, array_accum(row(a,b,c)::foo)
  FROM data
  GROUP BY id;

> Why doesn't this work?

Arrays of arrays aren't directly supported; you currently have to put
them into a tuple first.  Something like:

  CREATE TYPE bar AS ( a TEXT[] );
  SELECT array_agg(row(array['a'])::bar);

--
  Sam  http://samason.me.uk/

On Wed, Oct 28, 2009 at 11:17 AM, Viktor Rosenfeld
<listuser36@googlemail.com> wrote:
> Hi,
>
> I'm trying to aggregate a list of table attributes into an array.
>
> The actual code looks something like this:
>
>  SELECT
>    node_ref AS id,
>    array_agg(DISTINCT ARRAY[namespace, name, value]) as annotations
>  ...
>  GROUP BY id;
>
> I guess the minimal example that reproduces the error is:
>
>  annis=> select array_agg(array['a'::varchar, 'b', 'c']);
>  ERROR:  could not find array type for data type character varying[]
>
> Why doesn't this work?

There are no arrays of arrays.  There are however multi dimension
arrays and arrays of composite types (which may contain arrays).
Pick your poison.  Also, prefer array() to array_agg if you are not
truly aggregating (hard to tell in this query).

your problem:
postgres=# select array(select current_schemas(true));
ERROR:  could not find array type for datatype name[]

possible fix:
postgres=# select array(select row(current_schemas(true)));
                   ?column?
-----------------------------------------------
 {"(\"{pg_catalog,public,dblink,pgcrypto}\")"}

another way:
postgres=# select (v[1]).n[1] from (select array(select
row(current_schemas(true))::a) as v) q;
     n
------------
 pg_catalog
(1 row)

head spinning yet? :-)

merlin

Hi,

this looks good, but it does not work with DISTINCT.

  CREATE TYPE annotation AS ( namespace varchar, name varchar, value varchar );

  SELECT
    node.id as id,
    array_agg(DISTINCT ROW(namespace, name, value)::annotation) as annotation
  ...
  GROUP BY id

produces:

  ERROR:  could not identify an equality operator for type annotation

I tried to create a custom operator like this:

  CREATE OR REPLACE FUNCTION annotation_equal(lhs annotation, rhs annotation) RETURNS boolean AS $$
  BEGIN
    RETURN
      lhs.namespace = rhs.namespace AND
      lhs.name = rhs.name AND
      lhs.value = rhs.value;
  END;
  $$ LANGUAGE plpgsql IMMUTABLE;

  CREATE OPERATOR = (
    PROCEDURE = annotation_equal,
    LEFTARG = annotation,
    RIGHTARG = annotation
  );

But it doesn't seem to get picked up.  If I leave out the DISTINCT in
the first SELECT query, it works as expected.  I could leave it out, but
then application logic would be more complex.  (Well, not really, I'd
just use a Set and not a List in Java, but it would increase the amount
of data send over the network and the reason I'm aggregating in the
first place is to minimize the data.)

I've also tried this:

  CREATE OR REPLACE FUNCTION annotation_hash(rhs annotation) RETURNS INTEGER AS $$
  BEGIN
    RETURN hashtext (rhs.namespace || '-' || rhs.name || '-' || rhs.value);
  END;
  $$ LANGUAGE plpgsql IMMUTABLE STRICT;

  CREATE OPERATOR CLASS annotation_ops DEFAULT FOR TYPE annotation USING hash AS
    OPERATOR 1 =,
    FUNCTION 1 annotation_hash(rhs annotation)
  ;

But then the error message changes to:

  ERROR:  cache lookup failed for operator 34755

Any idea?

Cheers,
Viktor

Sam Mason wrote:

> On Wed, Oct 28, 2009 at 04:17:32PM +0100, Viktor Rosenfeld wrote:
> > I'm trying to aggregate a list of table attributes into an array.
>
> I'd suggest using a tuple, arrays for things where each element means
> the same thing.  I'd guess you care about the substructure (i.e. the
> element has a "namespace", a "name" and a "value") and hence using an
> array in the first place seems wrong.  Maybe something like:
>
>   CREATE TYPE foo AS ( namespace TEXT, name TEXT, value TEXT );
>   SELECT id, array_accum(row(a,b,c)::foo)
>   FROM data
>   GROUP BY id;
>
> > Why doesn't this work?
>
> Arrays of arrays aren't directly supported; you currently have to put
> them into a tuple first.  Something like:
>
>   CREATE TYPE bar AS ( a TEXT[] );
>   SELECT array_agg(row(array['a'])::bar);
>
> --
>   Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Viktor Rosenfeld <listuser36@googlemail.com> writes:
> this looks good, but it does not work with DISTINCT.

>   CREATE TYPE annotation AS ( namespace varchar, name varchar, value varchar );

>   ERROR:  could not identify an equality operator for type annotation

My recollection is you need a complete btree operator class to support
DISTINCT.  8.4 would provide that automatically for composite types,
but in 8.3 you'll have to build it yourself.

> I've also tried [ a hash opclass ]
> But then the error message changes to:
>   ERROR:  cache lookup failed for operator 34755

That's not what I get when I copy your example, so I think you left
something out.

            regards, tom lane