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
142
143
|
---
description: This rule explains PostgreSQL database design patterns and advanced features usage.
globs: **/*.sql
alwaysApply: false
---
# PostgresSQL rules
## 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
```sql
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:
```sql
select *
from employees
where end_date is null;
update employees
set end_date = '2023-12-31'
where employee_id = 1001;
```
Larger queries:
```sql
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.
```sql
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.
```sql
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.
```sql
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;
```
|
