PostgreSQL: Undefined function array_agg(enumlabel)

Question

I am newbie in PostgreSQL. When I apply migrations on the hosting I see an error:

SQLSTATE[42883]: Undefined function: 7
ERROR: function array_agg(name) does not exist
LINE 12: array_to_string((select array_agg(enumlabel) from pg_enum...

My hosting has PostgreSQL 8.3.11.

The SQL being executed:

SELECT
    d.nspname AS table_schema,
    c.relname AS table_name,
    a.attname AS column_name,
    t.typname AS data_type,
    a.attlen AS character_maximum_length,
    pg_catalog.col_description(c.oid, a.attnum) AS column_comment,
    a.atttypmod AS modifier,
    a.attnotnull = false AS is_nullable,
CAST(pg_get_expr(ad.adbin, ad.adrelid) AS varchar) AS column_default,
coalesce(pg_get_expr(ad.adbin, ad.adrelid) ~ 'nextval',false) AS is_autoinc,

array_to_string((select array_agg(enumlabel)
from pg_enum
where enumtypid=a.atttypid)::varchar[],',') as enum_values,

CASE atttypid
    WHEN 21 /*int2*/ THEN 16
    WHEN 23 /*int4*/ THEN 32
    WHEN 20 /*int8*/ THEN 64
    WHEN 1700 /*numeric*/ THEN
CASE WHEN atttypmod = -1
THEN null
ELSE ((atttypmod - 4) >> 16) & 65535
END
    WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
    WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
ELSE null
END AS numeric_precision,
CASE
    WHEN atttypid IN (21, 23, 20) THEN 0
    WHEN atttypid IN (1700) THEN
CASE
    WHEN atttypmod = -1 THEN null
ELSE (atttypmod - 4) & 65535
END
ELSE null
END AS numeric_scale,
CAST(information_schema._pg_char_max_length(information_schema._pg_truetypid(a, t),
information_schema._pg_truetypmod(a, t)) AS numeric) AS size,
a.attnum = any (ct.conkey) as is_pkey
FROM
pg_class c
    LEFT JOIN pg_attribute a ON a.attrelid = c.oid
    LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
    LEFT JOIN pg_type t ON a.atttypid = t.oid
    LEFT JOIN pg_namespace d ON d.oid = c.relnamespace
    LEFT join pg_constraint ct on ct.conrelid=c.oid and ct.contype='p'
WHERE
    a.attnum > 0 and t.typname != ''
    and c.relname = 'migration'
    and d.nspname = 'public'
ORDER BY
    a.attnum;

in .../vendor/yiisoft/yii2/db/Schema.php:628

Before you do anything else, consider the [versioning page of Postgres](http://www.postgresql.org/support/versioning/). Postgres 8.3 has reached EOL in 2013 and is unsupported and just too old for any serious work. Upgrade to a current version. — Erwin Brandstetter, Oct 21 '15 at 21:58

score 6 · Answer 1 · edited May 23 '17 at 12:15

Like commented, your best option is to update to a current version of Postgres. There is no array_agg() function in Postgres 8.3.

However, you can substitute with a basic ARRAY constructor, which has been there since at least Postgres 7.4 and happens to be more efficient for this anyway, even in current Postgres. Replace the bolded part of the query in your question with:

array_to_string(ARRAY(
   SELECT enumlabel FROM pg_enum
   WHERE  enumtypid = a.atttypid)::text[], ',') AS enum_values,

In Postgres 9.0 or later you can simplify with string_agg():

(SELECT string_agg(enumlabel, ',') FROM pg_enum
 WHERE  enumtypid = a.atttypid) AS enum_values,

The simple ARRAY constructor should still be faster, though.
BTW, pg_enum itself was introduced with Postgres 8.3 (for enum types).

score 2 · Answer 2 · edited Oct 22 '15 at 03:09

2

There is no array_agg function in PostgreSQL 8.3. See the release notes.

edited Oct 22 '15 at 03:09

arogachev

33,150
7
114
117

answered Oct 21 '15 at 21:35

randomnoun

21
1

Christian · Answer 3 · 2015-10-22T14:40:15.473

There's no array_agg in Postgresql 8.3.

But if you just cant't upgrade, you can then just create array_agg function as easy as this:

CREATE AGGREGATE array_agg (
    sfunc = array_append,
    basetype = anyelement,
    stype = anyarray,
    initcond = '{}'
);

NOT DIRECTLY RELATED TO THE QUESTION:

If you choose to upgrade from 8.x to 9.x version, some (bad written) queries will not work any more... specially those with implicit casts:

select * from my_table where my_int_column = some_text_value

So, you have to rewrite them adding explicit cast:

select * from my_table where my_int_column = some_text_value::int

or you can create some "autocasts" in way of keep them working while you rewrite them down:

CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;
CREATE CAST (character AS integer) WITH INOUT AS IMPLICIT;

PostgreSQL: Undefined function array_agg(enumlabel)

3 Answers3