Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work.

In the below, all I did this far is replace IFNULL() with COALESCE() and UNIX_TIMESTAMP() with TO_TIMESTAMP()::TIMESTAMP

I don't know what the error means. The query worked just fine in MySQL, and I'm not sure, after looking at pages discussing differences between it and PostgreSQL, what the problem is (I also don't know what to replace the SEC_TO_TIME line with, but removing that line doesn't fix the error).

SELECT IFNULL(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
    (CASE
      WHEN t.id > 0 THEN (CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END)
      ELSE 'font-weight: bold'
    END) AS __style__,
    t.id AS ticket,
    IF(t.id > 0, t.summary, 'Total') AS summary,
    SEC_TO_TIME(SUM(IF(w.endtime, w.endtime, TO_TIMESTAMP(NOW())::TIMESTAMP) - w.starttime)) AS total
  FROM ticket t
  INNER JOIN work_log w
  WHERE t.id = w.ticket
  GROUP BY t.component, t.id, t.summary, t.status
  WITH ROLLUP HAVING IFNULL(id, -1) = -1 OR (t.summary IS NOT NULL AND t.status IS NOT NULL);

ERROR:  syntax error at or near "WHERE"
LINE 11:  WHERE t.id = w.ticket

Edit: I tried changing the WHERE to ON, and that seemed to fix that error; however, I don't know if this is the correct solution. Also, now I get an error with the "WITH ROLLUP HAVING" part. What's the way to do it in PostgreSQL?

Also, I don't know what to replace SEC_TO_TIME with above, or the SUM / IF combo (as IF() is MySQL only--if I swap to CASE, do I need to test separately for 0 and null?). Using TO_CHAR as per http://www.verious.com/qa/sec-to-time-function-in-postgre-sql/ doesn't work because the minutes are always zero with the code given there (i.e. 90 seconds ends up as 00:00:30...)

Edit 2: What I'm trying to end up with, which was the case with MySQL: http://i62.tinypic.com/flljb5.png

Edit 3: Thanks to a_horse_with_no_name and efesar, I've ended up with the following working query:

WITH base AS (
  SELECT t.component AS component, t.status AS status, t.id AS ticket, t.summary AS summary,
      SUM((CASE WHEN w.endtime > 0 THEN w.endtime ELSE CAST(EXTRACT(EPOCH FROM current_timestamp) AS bigint) END) - w.starttime) * INTERVAL '1 second' AS total
    FROM ticket t
    INNER JOIN work_log w
    ON t.id = w.ticket
    GROUP BY t.component, t.id, t.summary, t.status
)
SELECT CONCAT('Component ', component) AS __group__,
    (CASE status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END) AS __style__,
    ticket, summary, total
  FROM base
UNION ALL
SELECT CONCAT('Component ', component) AS __group__,
  'font-weight: bold' as __style__, null, 'Total', SUM(total)
  FROM base
  GROUP BY __group__
UNION ALL
SELECT 'All Components' AS __group__, 'font-weight: bold' as __style__, null, 'Total', SUM(total)
  FROM base
ORDER BY __group__, ticket
share|improve this question
    
I thought PostgreSQL supported joins in the where clause, but perhaps not. Try changing the keyword WHERE to ON. As for WITH ROLLUP HAVING ... In PostgreSQL you would use Window functions using the OVER() clause. postgresql.org/docs/9.3/static/functions-window.html –  efesar Apr 3 at 19:59
    
Also, I don't think PG has an IFNULL function. Use COALESCE instead. I don't think it has an IF function, so you'll use a CASE instead. SEC_TO_TIME and TO_TIMESTAMP ... yeah, there are a lot of functions you'll have to convert. –  efesar Apr 3 at 20:16
    
COALESCE works as a replacement for IFNULL. But with replacing that IF() with a CASE, do I have to explicitly do a comparison with both 0 and null, or what's the proper way? –  Display Name Apr 3 at 20:35
    
INNER JOIN work_log w WHERE t.id = w.ticket is completely invalid and I would be very suprised if MySQL was actually running that. The JOIN is missing the actual join condition defined by the ON clause. But Postgres unfortunately does not support the with rollup part –  a_horse_with_no_name Apr 3 at 20:42
    
What's the alternative? I'm trying to get the same ticket report appearance as I had in MySQL, with subtotals, as per the attachment I've just added. –  Display Name Apr 3 at 21:30

2 Answers 2

up vote 4 down vote accepted

Something like this:

SELECT coalesce(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
       CASE
          WHEN t.id > 0 THEN CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END
          ELSE 'font-weight: bold'
        END AS __style__,
        t.id AS ticket,
        case when t.id > 0 then t.summary else 'Total' end AS summary,
        SUM( coalesce(w.endtime, current_timestamp) - w.starttime) AS total
FROM ticket t
  INNER JOIN work_log w ON t.id = w.ticket
GROUP BY t.component, t.id, t.summary, t.status

The sum() will sum up the difference in milliseconds between endtime and starttime not sure what the intention behind that is.

I'm not entirely sure what the rollup does, but something along the lines:

with base_data as (
   ... the above query goes here ...
)
select __group__, ticket, __style__, summary, total
from base_data
union all
select null, null, null, null, sum(total)
from base_data
group by __group__, ticket, __style__, summary 

might get you started.

share|improve this answer
    
Hi a_horse_with_no_name, the time sum intention is to see the time difference between start and end time (or start and current time, if there's no end time set), and show the output in pretty print, something like "6 days, 3:25:21" –  Display Name Apr 3 at 22:27
    
@DisplayName If starttime and endtime are timestamp columns, then endtime - startime yields an interval and you can directly sum() them. No need to use extract() or something similar –  a_horse_with_no_name Apr 3 at 22:30
    
@a_horse_with_no_name when the GROUP BY has 4 columns, the ROLLUP modifier will show 4 levels of aggregates. It will be a pain in the as to write in Postgres (write the window funnctios and manage to get the same ordering in the end.) –  ypercube Apr 3 at 23:02
    
@ypercube: a thanks. I hever used rollup (not even in Oracle) so I was just guessing –  a_horse_with_no_name Apr 4 at 5:40

Okay, so taking most of my comments and forming them into an answer:

  1. PostgreSQL doesn't support JOIN conditions in the WHERE clause. Use an ON clause instead.
  2. ROLLUP HAVING is typically replaced with Window functions using the OVER clause. I'm not an expert on the ROLLUP HAVING but I might be able to clarify the Window functions if you need.
  3. Replace the IFNULL with the COALESCE function.
  4. Replace the IF function with the CASE expression.
  5. Try replacing the SEC_TO_TIME functions with a slightly different usage of the AGE function. The AGE function returns INTERVALs which can be summed. Here is a SQL Fiddle example.
  6. In PG, you can subtract two TIMESTAMPs to get an INTERVAL (so endtime - starttime might return something like '42 days').

I hope that covers everything. I'll add updates as needed.

share|improve this answer
1  
Postgres does support join conditions in the where clause, it just doesn't support an incomplete JOIN operator –  a_horse_with_no_name Apr 3 at 22:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.