Rounding
SELECT round(3.567, 2); -- 3.57
SELECT round(3.567); -- 4
SELECT ceil(3.2), floor(3.9);
SELECT trunc(3.567, 1); -- 3.5 (cuts, does not round)
SELECT round(1234.5678, -2); -- 1200
round(x, n) with two arguments works on numeric. For a double precision value, cast first: round(x::numeric, 2).
Arithmetic
SELECT abs(-15), sign(-7), mod(10, 3), 10 % 3;
SELECT power(2, 10), sqrt(144), exp(1), ln(100), log(10, 1000);
SELECT greatest(3, 9, 2), least(3, 9, 2);
SELECT pi(), random(), floor(random() * 100 + 1)::int;
SELECT factorial(5), gcd(12, 18), lcm(4, 6);
Division behaviour
SELECT 7 / 2; -- 3 integer division!
SELECT 7.0 / 2; -- 3.5
SELECT 7::numeric / 2; -- 3.5
SELECT 7 / 0; -- ERROR: division by zero
Two differences from MySQL matter here: integer division truncates, and dividing by zero raises an error instead of returning NULL. Guard it:
SELECT total / nullif(count_col, 0) AS safe_avg FROM stats;
Formatting and casting
SELECT to_char(1234567.891, 'FM999,999,999.00');
SELECT '42'::int, 3.7::int, '3.14'::numeric;
SELECT cast('42' AS bigint);
SELECT pg_typeof(1), pg_typeof(1.0);
Sequences
CREATE SEQUENCE order_no_seq START 1000 INCREMENT 1;
SELECT nextval('order_no_seq');
SELECT currval('order_no_seq');
SELECT setval('order_no_seq', 5000);
-- the sequence behind a serial/identity column
SELECT pg_get_serial_sequence('students', 'id');
SELECT setval(pg_get_serial_sequence('students','id'), (SELECT max(id) FROM students));
That last statement is the fix for "duplicate key" errors after importing rows with explicit ids — the sequence was left behind and needs to be moved forward.
Sequences are not transactional: a rolled-back insert still consumes the number, so identity columns have gaps. That is by design and not a problem.
Statistical aggregates
SELECT
avg(cgpa), stddev(cgpa), variance(cgpa),
percentile_cont(0.5) WITHIN GROUP (ORDER BY cgpa) AS median,
percentile_cont(0.95) WITHIN GROUP (ORDER BY cgpa) AS p95,
mode() WITHIN GROUP (ORDER BY department) AS most_common_dept
FROM students;
Postgres has a real median through percentile_cont, which MySQL lacks entirely.
Practical examples
SELECT name_en, round(cgpa / 4.0 * 100, 1) AS percent FROM students;
SELECT item, round(price * 1.15, 2) AS price_with_vat FROM products;
SELECT floor(age / 10) * 10 AS age_group, count(*)
FROM students GROUP BY 1 ORDER BY 1;
SELECT width_bucket(cgpa, 0, 4, 4) AS quartile, count(*)
FROM students GROUP BY 1 ORDER BY 1;