AI Prompt: Postgres SQL Style Guide

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
# 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:```sqlcreate 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:```sqlselect *from employeeswhere end_date is null;update employeesset end_date = '2023-12-31'where employee_id = 1001;```Larger queries:```sqlselect  first_name,  last_namefrom  employeeswhere  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.```sqlselect  employees.employee_name,  departments.department_namefrom  employeesjoin  departments on employees.department_id = departments.department_idwhere  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.```sqlselect count(*) as total_employeesfrom employeeswhere 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.```sqlwith 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_employeesfrom  employee_countsorder by  department_name;```