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
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
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
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