Postgres SQL Style Guide

General

• Use lowercase for SQL reserved words to maintain consistency and readability.
• Employ consistent, descriptive identifiers for tables, columns, and other database objects.
• Use white space and indentation to enhance the readability of your code.
• Store dates in ISO 8601 format (yyyy-mm-ddThh:mm:ss.sssss).
• Include comments for complex logic, using '/_ ... _/' for block comments and '--' for line comments.

Naming Conventions

• Avoid SQL reserved words and ensure names are unique and under 63 characters.
• Use snake_case for tables and columns.
• Prefer plurals for table names
• Prefer singular names for columns.

Tables

• Avoid prefixes like 'tbl_' and ensure no table name matches any of its column names.
• Always add an id column of type identity generated always unless otherwise specified.
• Create all tables in the public schema unless otherwise specified.
• Always add the schema to SQL queries for clarity.
• Always add a comment to describe what the table does. The comment can be up to 1024 characters.

Columns

• Use singular names and avoid generic names like 'id'.
• For references to foreign tables, use the singular of the table name with the _id suffix. For example user_id to reference the users table
• Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception.

Examples:

create table books (
  id bigint generated always as identity primary key,
  title text not null,
  author_id bigint references authors (id)
);
comment on table books is 'A list of all the books in the library.';

Queries

• When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability
• Add spaces for readability.

Smaller queries:

select *
from employees
where end_date is null;

update employees
set end_date = '2023-12-31'
where employee_id = 1001;

Larger queries:

select
  first_name,
  last_name
from employees
where start_date between '2021-01-01' and '2021-12-31' and status = 'employed';

Joins and Subqueries

• Format joins and subqueries for clarity, aligning them with related SQL clauses.
• Prefer full table names when referencing tables. This helps for readability.

select
  employees.employee_name,
  departments.department_name
from
  employees
  join departments on employees.department_id = departments.department_id
where employees.start_date > '2022-01-01';

Aliases

• Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity.

select count(*) as total_employees
from employees
where end_date is null;

Complex queries and CTEs

• If a query is extremely complex, prefer a CTE.
• Make sure the CTE is clear and linear. Prefer readability over performance.
• Add comments to each block.

with
  department_employees as (
    -- Get all employees and their departments
    select
      employees.department_id,
      employees.first_name,
      employees.last_name,
      departments.department_name
    from
      employees
      join departments on employees.department_id = departments.department_id
  ),
  employee_counts as (
    -- Count how many employees in each department
    select
      department_name,
      count(*) as num_employees
    from department_employees
    group by department_name
  )
select
  department_name,
  num_employees
from employee_counts
order by department_name;