A style guide for SQL code.
- Tables have plural name
- Primary key is the singular form of the table name with `_pid`
- Foreign keys have the same name as the primary key of the table its referencing but with `_rid` (it should be _fid but there are legacy reason we use rid)
- Ids at the boundary of a function (inputs or return values) just use `_id`.
- Input params start with _ and match the table params when possible, and not an id.
- 4 space tabs
- Column align 4 spaces after longest for return type table and table definitions.
- 1 space indent for the function definition block after the initial line. This helps with code folding.
- indent AND / ON for WHERE / JOIN clauses
- All SQL keywords are uppercase
- SQL types and functions are lower case
- Functions with 1 SQL statement have no `;` internally to the function. If you have multiple statements, you need the `;`. Limit the number of functions that have multiple statements
- Calling a function from within a function can be simpler, but be very careful as it can be a performance limit. Simple helper functions are generally fine.
- Postgres uses WITH instead of temp tables.
- Postgres has a short had way to write queries that are only INNER JOINs
SELECT * FROM table1, table2 WHERE table1_id = table2_id;
Is the same as
SELECT * FROM table1
INNER JOIN table2
ON table1_id = table2_id;