Query

WITH — Common Table Expression. Define a "temp table" in a complex query.

RETURNING can return data after an DML operation.

UPDATE users SET name = 'juanito' where id = 1 RETURNING *

Useful after you updated/deleted data.

You can implement TOP 10 records of ... with TABLESAMPLE in 10-100ms for large table 500M+.

string_agg, array_agg

date_trunc

Window Function.