AI Prompt: Postgres SQL Style Guide

1
# Postgres SQL Style Guide
2
3
## General
4
5
- Use lowercase for SQL reserved words to maintain consistency and readability.
6
- Employ consistent, descriptive identifiers for tables, columns, and other database objects.
7
- Use white space and indentation to enhance the readability of your code.
8
- Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`).
9
- Include comments for complex logic, using '/* ... */' for block comments and '--' for line comments.
10
11
## Naming Conventions
12
13
- Avoid SQL reserved words and ensure names are unique and under 63 characters.
14
- Use snake_case for tables and columns.
15
- Prefer plurals for table names
16
- Prefer singular names for columns.
17
18
## Tables
19
20
- Avoid prefixes like 'tbl_' and ensure no table name matches any of its column names.
21
- Always add an `id` column of type `identity generated always` unless otherwise specified.
22
- Create all tables in the `public` schema unless otherwise specified.
23
- Always add the schema to SQL queries for clarity.
24
- Always add a comment to describe what the table does. The comment can be up to 1024 characters.
25
26
## Columns
27
28
- Use singular names and avoid generic names like 'id'.
29
- 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
30
- Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception.
31
32
#### Examples:
33
34
```sql
35
create table books (
36
  id bigint generated always as identity primary key,
37
  title text not null,
38
  author_id bigint references authors (id)
39
);
40
comment on table books is 'A list of all the books in the library.';
41
```
42
43
44
## Queries
45
46
- When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability
47
- Add spaces for readability.
48
49
Smaller queries:
50
51
52
```sql
53
select *
54
from employees
55
where end_date is null;
56
57
update employees
58
set end_date = '2023-12-31'
59
where employee_id = 1001;
60
```
61
62
Larger queries:
63
64
```sql
65
select
66
  first_name,
67
  last_name
68
from
69
  employees
70
where
71
  start_date between '2021-01-01' and '2021-12-31'
72
and
73
  status = 'employed';
74
```
75
76
77
### Joins and Subqueries
78
79
- Format joins and subqueries for clarity, aligning them with related SQL clauses.
80
- Prefer full table names when referencing tables. This helps for readability.
81
82
```sql
83
select
84
  employees.employee_name,
85
  departments.department_name
86
from
87
  employees
88
join
89
  departments on employees.department_id = departments.department_id
90
where
91
  employees.start_date > '2022-01-01';
92
```
93
94
## Aliases
95
96
- Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity.
97
98
```sql
99
select count(*) as total_employees
100
from employees
101
where end_date is null;
102
```
103
104
105
## Complex queries and CTEs
106
107
- If a query is extremely complex, prefer a CTE.
108
- Make sure the CTE is clear and linear. Prefer readability over performance.
109
- Add comments to each block.
110
111
```sql
112
with department_employees as (
113
  -- Get all employees and their departments
114
  select
115
    employees.department_id,
116
    employees.first_name,
117
    employees.last_name,
118
    departments.department_name
119
  from
120
    employees
121
  join
122
    departments on employees.department_id = departments.department_id
123
),
124
employee_counts as (
125
  -- Count how many employees in each department
126
  select
127
    department_name,
128
    count(*) as num_employees
129
  from
130
    department_employees
131
  group by
132
    department_name
133
)
134
select
135
  department_name,
136
  num_employees
137
from
138
  employee_counts
139
order by
140
  department_name;
141
```