Short story about PostgreSQL SUM function

Here is a simple PostgreSQL type:

CREATE TYPE foo_t AS (
 id    integer,
 total bigint
);

and a simple query wrapped in stored procedure:

CREATE FUNCTION group_foo()
 RETURNS SETOF foo_t
 LANGUAGE "SQL"
AS $$
 SELECT id, SUM(some_column) FROM some_table GROUP BY id;
$$;

Now, we want to sum everything:

CREATE FUNCTION total_foo()
 RETURNS bigint -- same as foo_t.total
 LANGUAGE "SQL"
AS $$
 SELECT SUM(total) FROM group_foo();
$$;

And we have an error about type inconsistency!

This is caused by SUM function -- in PostgreSQL there are many variants of this function, as the db engine supports function name overriding (sounds familiar for C++ guys). There are following variants in PostgreSQL 9.1:

$ \df sum
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type 
------------+------+------------------+---------------------+------
 pg_catalog | sum  | numeric          | bigint              | agg
 pg_catalog | sum  | double precision | double precision    | agg
 pg_catalog | sum  | bigint           | integer             | agg
 pg_catalog | sum  | interval         | interval            | agg
 pg_catalog | sum  | money            | money               | agg
 pg_catalog | sum  | numeric          | numeric             | agg
 pg_catalog | sum  | real             | real                | agg
 pg_catalog | sum  | bigint           | smallint            | agg

Smaller types are promoted: from integer we get bigint, from bigint we get numeric, and so on.

SQL surprise

Secret Hackers Rule #11: Hackers read manuals.

Recently I've discovered that PostgreSQL supports setting NULL on column referenced to another table (i.e. foreign constraint) before deleting a row:

CREATE TABLE foo (
 id integer PRIMARY KEY
);

CREATE TABLE bar (
 id integer PRIMARY KEY,
 foo_id integer REFERENCES foo ON DELETE SET NULL
);

INSERT INTO foo (id) VALUES (1);
INSERT INTO bar (id, foo_id) VALUES (1, 1);
DELETE FROM foo;

Without ON DELETE SET NULL server reports error that key foo.id = 1 is still referenced in table bar.

Speeding up LIKE '%text%' queries

Recently I did some experminets with n-grams indexes used to speed-up full text search in a plain SQL database. The results are really promising.

PostgreSQL: get selected rows with given order

Suppose that database stores some kind of dictionary and user picks some items, but wants to keep order. For example dictionary has entries with id=0..10, and user picked 9, 2, 4 and 0. This simple query does the job (query splitted):

foo = SELECT (ARRAY[9,2,4,0])[i] AS index, i AS ord FROM  generate_series(1, 4) AS i;
SELECT * FROM dictionary INNER JOIN (foo) ON dictionary.id=foo.index ORDER BY foo.ord