0

I have two tables, posts and stars. When the rank (a post column, integer type) of a post entry (row) is changed, I want to update the priority (a post column, double precision type) value. The priority calculation needs to take into account a subquery from another table called stars (for star_count and total_stars as floats) which is dependent on whether an action (a stars column, boolean type) exists.

I thought that the problem might be related to the NULLIF statement in my COALESCE section improperly checking if the subquery exists (is null), but am not sure how else I could solve this.

UPDATE posts
SET priority = (
    COALESCE(
        NULLIF (subquery.star_count/CAST(subquery.total_stars AS FLOAT), ''),
        0.0
    ) + posts.rank)
FROM (
    SELECT sum(CASE action WHEN true THEN 1 ELSE 0 END) AS star_count, count(*) AS total_stars
    FROM stars
    WHERE post_id = $1
    ) AS subquery
WHERE posts.id = $1

Expectation:

  • if "action" exists (as either 1 OR 0), then calculate priority = (star_count / total_stars) + posts.rank
  • else if "action" doesn't exist, calculate priority = 0.0 + posts.rank

Unfortunately, anytime I trigger a change of the rank value of a posts entry with no existing "action", I receive the error:

ERROR 22P02 (invalid_text_representation) invalid input syntax for type double precision: ""

Should I instead be trying something with CASE and EXISTS?

Any suggestions would be greatly appreciated.

Edit: after trying @Erwin Brandstetter's solution, the calculation of priority doesn't yield the expected result.

When testing the scenario where action does not exist yet (intentionally):

Attempt  |  Rank  | Priority (actual) | Priority (expected) | Action
1        |  0     |    null           | 0                   | null
2        |  1     |    0              | 1                   | null
3        |  0     |    1              | 0                   | null
4        |  1     |    0              | 1                   | null
5        |  2     |    1              | 2                   | null
6        |  3     |    2              | 3                   | null
7        |  4     |    3              | 4                   | null
8        |  5     |    4              | 5                   | null
9        |  1     |    5              | 1                   | null
10       |  2     |    1              | 2                   | null
11       |  4     |    2              | 4                   | null

Priority does seem to get calculated after each update, but why does it not follow the else statement correctly?

BananaNTie
  • 3
  • 5

1 Answers1

0

That should fix the error, avoid division by zero properly, and perform better:

UPDATE posts p
SET    priority =
         CASE WHEN sub.star_count > 0  -- both > 0, follows logically
            THEN p.rank + sub.star_count / sub.total_stars::float
            ELSE p.rank
         END
FROM  (
    SELECT count(*) FILTER (WHERE action) AS star_count
         , count(*)::float AS total_stars
    FROM   stars
    WHERE  post_id = $1
    ) sub
WHERE  p.id = $1;

Assuming actionis a boolean type.

The problem was in this expression:

NULLIF (subquery.star_count/CAST(subquery.total_stars AS FLOAT), '')

The empty string ('') has no representation in double precision (float). Hence the error.

About the aggregate FILTER clause:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the quick response.It no longer errors out and certainly performs faster, but the results don't appear reflect expected outcome. See updated post. – BananaNTie Jul 23 '19 at 13:32
  • I'm still a little confused about the aggregate `FILTER` clause. Does your suggestion assign binary vaules and sum up the boolean `action` as intended from my approach? – BananaNTie Jul 23 '19 at 13:52
  • It would be much easier to tell with actual `CREATE TABLE` statements. Or a fiddle, even. Random example: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=14cad1559687ac0e0bc8853e377814bb – Erwin Brandstetter Jul 23 '19 at 14:10