DIV and GROUP by issues

Question

I have one query, made it in MySQL.

And now try to run on PostgreSQL, but get syntax error twice.

Once on the DIV command, and then on the GROUP BY.

Here is my code:

UPDATE status_table 
SET Status_CG = true
WHERE EXISTS ( SELECT MAX(m2.t1 - m1.t1) as difference 
               FROM measurements AS m1 
               INNER JOIN measurements AS m2 
                  ON `m2`.`Measurements_ID` 
                     > m1.Measurements_ID 
               AND (m2.Measurements_ID -1) DIV 10 
                    = ( m1.Measurements_ID - 1) DIV 10
               GROUP BY (m1.Measurements_ID - 1 ) DIV 10 
               HAVING MAX(m2.t1 - m1.t1) >= 100
              );

Answer 1

just doing a direct translation, this would run (three main changes applied):

• removal of backticks ` `
• quoting of objects (tables, columns, etc) with uppercase characters "MyName"
• replacement of DIV with /:

UPDATE status_table
SET "Status_CG" = true
WHERE EXISTS ( SELECT MAX(m2.t1 - m1.t1) as difference
               FROM measurements AS m1
               INNER JOIN measurements AS m2
                  ON m2."Measurements_ID"
                     > m1."Measurements_ID"
               AND (m2."Measurements_ID" -1) / 10
                    = ( m1."Measurements_ID" - 1) / 10
               GROUP BY (m1."Measurements_ID" - 1 ) / 10
               HAVING MAX(m2.t1 - m1.t1) >= 100
              );

If possible, you should consider just using all lowercase names for your tables and columns to avoid the need to quote to preserve uppercase characters in the name. For further info, here is a good post on identifiers and quoting.

Hope this is helpful in illustrating the syntax differences needed for your query to run