PostgreSQL: order by sum of computed values

Question

I have a table tips that is defined as follows:

CREATE TABLE tips
(
  tip_id bigserial NOT NULL,
  tip text NOT NULL,
  author text NOT NULL,
  post_date bigint NOT NULL,
  likers character varying(16)[],
  dislikers character varying(16)[],
  likes integer NOT NULL,
  dislikes integer NOT NULL,
  abuse_history character varying(16)[]
);

I need to get the tips based on popularity, with the definition of popularity being:
likes - dislikes - (size(abuse_history)*5)

The query below gives me the same results regardless of the sort order (ASC/DESC).

select * from tips order by (likes - dislikes - (array_length(abuse_history,1) * 5)) ASC limit 2147483647 offset 0

EDIT

I inserted 3 records that have the following values:
1) 1 like, 0 dislikes, 0 abuse complaints
2) 0 likes, 1 dislike, 0 abuse complaints
3) 0 likes, 0 dislikes, 0 abuse...

Regardless of the sort order (ASC/DESC), I get the following order: {3, 1, 2}

Could anyone please point me in the right direction?

Answer 1

Consider this:

SELECT array_length('{}'::character varying(16)[],1);

Output is NULL for an empty array. Also, your abuse_history can be NULL itself. So you need something like this:

SELECT *
  FROM tips
 ORDER BY (likes - dislikes - COALESCE(array_length(abuse_history,1) * 5, 0)) DESC;

EDIT after feedback:

Works in PostgreSQL 9.0 as shown in this demo:

CREATE TABLE tips
( tip_id bigserial NOT NULL,
  tip text,
  author text,
  post_date bigint,
  likers character varying(16)[],
  dislikers character varying(16)[],
  likes integer,
  dislikes integer,
  abuse_history character varying(16)[]
);


INSERT INTO tips (likes, dislikes, abuse_history)
VALUES(1,0, '{}')
,(1,0, '{}')
,(0,1, '{}')
,(0,0, '{}')
,(1,0, '{stinks!,reeks!,complains_a_lot}');


SELECT tip_id
        , likes
        , dislikes
        , (likes - dislikes - COALESCE(array_upper(abuse_history,1) * 5,0)) as pop
        , (likes - dislikes - array_upper(abuse_history,1) * 5) as fail_pop
  FROM tips
 ORDER BY (likes - dislikes - COALESCE(array_upper(abuse_history,1) * 5,0)) DESC;

Output:

 tip_id | likes | dislikes | pop | fail_pop
--------+-------+----------+-----+----------
      1 |     1 |        0 |   1 |
      2 |     1 |        0 |   1 |
      4 |     0 |        0 |   0 |
      3 |     0 |        1 |  -1 |
      5 |     1 |        0 | -14 |      -14

Answer 2

In order to debug this, put the same expression of the ORDER BY clause into the SELECT part. Then examine the results - are they really what you expect?

select *, (likes - dislikes - (array_length(abuse_history,1) * 5)) 
from tips 
order by (likes - dislikes - (array_length(abuse_history,1) * 5)) ASC 
limit 2147483647 offset 0

Oh, and BTW, strip that silly LIMIT and OFFSET thing.