ERROR: function unnest(integer[]) does not exist in postgresql

Question

SELECT UNNEST(ARRAY[1,2,3,4])

While executing the above query I got the error like this:

ERROR: function unnest(integer[]) does not exist in postgresql.

I am using PostgreSQL 8.3 and I have installed the _int.sql package in my db for integer array operation.

How to resolve this error?

Solution

unnest() is not part of the module intarray, but of standard PostgreSQL. However, you need version 8.4 or later for that.

So you can resolve this by upgrading to a more recent version, preferably the current version 9.1. See the versioning policy of the PostgreSQL project.

If you should be using Heroku's shared database, which currently uses version 8.3, they are looking into upgrading, too. Heroku Labs already offers 9.1.

---

As @Abdul commented, you can implement a poor man's unnest() in versions before PostgreSQL 8.4 yourself:

CREATE OR REPLACE FUNCTION unnest(anyarray)
  RETURNS SETOF anyelement AS
$BODY$
   SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i;
$BODY$ LANGUAGE sql IMMUTABLE;

However, be aware that this only works for one-dimensional arrays. (As opposed to PostgreSQL's unnest() which takes arrays with multiple dimensions):

SELECT unnest('{1,2,3,4}'::int[])  -- works
SELECT unnest('{{1,2},{3,4},{5,6}}'::int[])  -- fails! (returns all NULLs)

You could implement more functions for n-dimensional arrays:

CREATE OR REPLACE FUNCTION unnest2(anyarray) -- for 2-dimensional arrays
  RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i][j]
FROM  (
    SELECT i, generate_series(array_lower($1,2), array_upper($1,2)) j
    FROM  (
        SELECT generate_series(array_lower($1,1), array_upper($1,1)) i
        ) x
    ) y;
$BODY$ LANGUAGE sql IMMUTABLE;

Call:

SELECT unnest2('{{1,2},{3,4},{5,6}}'::int[])  -- works!

You could also write a PL/pgSQL function that deals with multiple dimensions ...

Comments

[Rafiu]

Thanks for the reply. (CREATE OR REPLACE FUNCTION unnest(anyarray) RETURNS SETOF anyelement AS $BODY$ SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ LANGUAGE 'sql' IMMUTABLE) by creating function like this I implemented the unnest function in postgresql 8.3

[Erwin Brandstetter]

@AbdulRafiu: Yeah, that should do. I included you comment into my answer and added a bit.