Notes and tips for using Postgresql

postgresql_notes.md

PostgreSQL Notes

Column Names

All identifiers are coerced to lower-case so column names are effectively case-insensitive. To avoid this, quote the identifier with double quotes.

SELECT "myColA" FROM "camelCaseTable";

Analysing Queries

Prepend the following

explain (analyze on, timing on) 

Remove the timing keyword if you don't want to actually execute the query.

Windowing

SELECT
    *,
    LAG(col, 1) OVER w AS previous_col,
    RANK() OVER w AS "rank"
FROM
    table
WINDOW w AS (
    PARTITION BY col1, col2
    ORDER BY col3
);