---
# Specify the following for Cursor rules
description: Guidelines for writing Postgres SQL
alwaysApply: false
---
# 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:[Link]`).
- 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;
```