Array arithmetic used to be difficult until all of the nice interfaces raised by the recent improvements by arrays. I once wrote these functions in C, but now they can be written more simply in plpgsql.

The array arithmetic function we have here will add two integer arrays together.

   select array_add('{1,2,3}', '{1,1,1}');
   array_add
   -----------
   {2,3,4}

   select array_add('{1,2,3}', '{1,1,1,9}');
   array_add
   -----------
   {2,3,4,9}

Here is the function definition for array addition with an example run.

   CREATE TABLE arraytest (
   id character varying(10) NOT NULL,
   somearr integer[]
   );

   INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
   INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');

   CREATE OR REPLACE FUNCTION array_add(int[],int[]) RETURNS int[] AS '
    DECLARE
      x ALIAS FOR $1;
      y ALIAS FOR $2;
      a int;
      b int;
      i int;
      res int[];
    BEGIN
      res = x;
   
      a := array_lower (y, 1);
      b := array_upper (y, 1);
   
      IF a IS NOT NULL THEN
        FOR i IN a .. b LOOP
          res[i] := coalesce(res[i],0) + y[i];
        END LOOP;
      END IF;

      RETURN res;
    END;
   ' LANGUAGE plpgsql STRICT IMMUTABLE;

The combination of the loop from a to b and the coalesce of the res value enable the arrays to be different sizes. Here are test results of the function with same sized arrays and different sized arrays.

   CREATE TABLE arraytest (
      id character varying(10) NOT NULL,
      somearr integer[]
   );

   INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
   INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');

   select * from arraytest;
    id | somearr
   ----+---------
    a  | {1,2,3}
    b  | {0,1,2}

   select array_add( somearr, '{1,1,1}' ) from arraytest;
    array_add
   -----------
    {2,3,4}
    {1,2,3}

   select array_add( somearr, '{1,1,1,1}' ) from arraytest;
    array_add
   -----------
    {2,3,4,1}
    {1,2,3,1}

   select array_add( somearr, '{1,1}' ) from arraytest;
    array_add
   -----------
    {2,3,3}
    {1,2,2}

Having a function to add arrays is very useful, but it can be even more useful if we use it to define an addition operator and a sum aggregate. Since the function is already written and tested it is only a matter of plugging them in.

In the first case we will add an + operator and tie it into the array_add function, specifying that the left and the right arguments are both integer arrays. Then we can just use the operator instead of explicitly invoking the function. For adding two literal arrays, you may have to explicitly cast one to ::int[] for the operator to be found.

   CREATE OPERATOR +  (
     PROCEDURE = array_add,
     LEFTARG = int[],
     RIGHTARG = int[]
   );

   select somearr + '{1,1,1,1}' from arraytest;
    ?column?
   -----------
    {2,3,4,1}
    {1,2,3,1}

Now we have a sensible way of adding arrays, we will want to sum arrays. We will define an aggregate named sum which will use the function array_add to operate on integer array types. This is overloading the sum() function so that now we can use it on these types.

   CREATE AGGREGATE sum (
      sfunc = array_add,
      basetype = INTEGER[],
      stype = INTEGER[],
      initcond = '{}'
   );
   
   select sum(somearr) from arraytest;
      sum
   ---------
    {1,3,5}

The obvious next steps include defining subtraction, multiplication and division for integer arrays in order to have a full set of arithmetic functions and operators. This is an exercise left to the reader ;)

The code used in this article can be found here. Contributors: Tony Wasson ajwasson at gmail.com, elein at varlena.com

Dropping and recreating Superusers [GENERAL] alter table owner doesn't update acl information 10-May-2005

In a database there are superusers who own the views that are used by the ordinary users. The ordinary users do not access the underlying tables except through views.

As happens in the real world, one superuser left and another one was added. However, all of the views owned by the first superuser were inaccessible to users.

To prevent this problem from happening, you should create the new superuser with the same system identifyer as the old superuser. This way the new superuser will assume ownership of the views and there should be no access problems for the users. To create a new superusers with an existing sysid use:

	CREATE USER newpgadmin WITH SYSID 123;

Contributors: Madeleine Theile madeleine.theile at cs.uni-dortmund.de Bruce Momjian pgman at candle.pha.pa.us Tom Lane tgl at sss.pgh.pa.us

Adding SERIAL primary keys to a table [GENERAL] Mass Import/Generate PKs 06-Nov-2004

As part of a very large copy of data into a new table, a new PRIMARY KEY needed to be generated. In this example, an email list was to be imported.

The table is created and imported without a primary key using psql.

	create table emaillist as ( login text );
	\copy emaillist from '/data/files/email.data'

	ALTER TABLE emaillist ADD id SERIAL;
	ALTER TABLE emaillist ADD CONSTRAINT emaillist_pk PRIMARY KEY (id);

Contributors: Hunter Hillegas lists at lastonepicked.com, Peter Eisentraut peter_e at gmx.net, Franco Bruno Borghesi franco at akyasociados.com.ar, Ed L. pgsql at bluepolka.net, Goutam Paruchuri gparuchuri at oneil.com

General Bits Missing Issue 15-May-2005

I humbly apologize for missing a new issue last week. What can I say? It is happens sometimes that work and life get in the way :)

Editor: elein at varlena.com