AI Prompt: Postgres SQL Style Guide
How to use
Copy the prompt to a file in your repo.
Use the "include file" feature from your AI tool to include the prompt when chatting with your AI assistant. For example, with GitHub Copilot, use #<filename>, in Cursor, use @Files, and in Zed, use /file.
Prompt
_147---_147# Specify the following for Cursor rules_147description: Guidelines for writing Postgres SQL_147globs: "**/*.sql"_147---_147_147# Postgres SQL Style Guide_147_147## General_147_147- Use lowercase for SQL reserved words to maintain consistency and readability._147- Employ consistent, descriptive identifiers for tables, columns, and other database objects._147- Use white space and indentation to enhance the readability of your code._147- Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`)._147- Include comments for complex logic, using '/* ... */' for block comments and '--' for line comments._147_147## Naming Conventions_147_147- Avoid SQL reserved words and ensure names are unique and under 63 characters._147- Use snake_case for tables and columns._147- Prefer plurals for table names_147- Prefer singular names for columns._147_147## Tables_147_147- Avoid prefixes like 'tbl_' and ensure no table name matches any of its column names._147- Always add an `id` column of type `identity generated always` unless otherwise specified._147- Create all tables in the `public` schema unless otherwise specified._147- Always add the schema to SQL queries for clarity._147- Always add a comment to describe what the table does. The comment can be up to 1024 characters._147_147## Columns_147_147- Use singular names and avoid generic names like 'id'._147- 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_147- Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception._147_147#### Examples:_147_147```sql_147create table books (_147 id bigint generated always as identity primary key,_147 title text not null,_147 author_id bigint references authors (id)_147);_147comment on table books is 'A list of all the books in the library.';_147```_147_147_147## Queries_147_147- When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability_147- Add spaces for readability._147_147Smaller queries:_147_147_147```sql_147select *_147from employees_147where end_date is null;_147_147update employees_147set end_date = '2023-12-31'_147where employee_id = 1001;_147```_147_147Larger queries:_147_147```sql_147select_147 first_name,_147 last_name_147from_147 employees_147where_147 start_date between '2021-01-01' and '2021-12-31'_147and_147 status = 'employed';_147```_147_147_147### Joins and Subqueries_147_147- Format joins and subqueries for clarity, aligning them with related SQL clauses._147- Prefer full table names when referencing tables. This helps for readability._147_147```sql_147select_147 employees.employee_name,_147 departments.department_name_147from_147 employees_147join_147 departments on employees.department_id = departments.department_id_147where_147 employees.start_date > '2022-01-01';_147```_147_147## Aliases_147_147- Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity._147_147```sql_147select count(*) as total_employees_147from employees_147where end_date is null;_147```_147_147_147## Complex queries and CTEs_147_147- If a query is extremely complex, prefer a CTE._147- Make sure the CTE is clear and linear. Prefer readability over performance._147- Add comments to each block._147_147```sql_147with department_employees as (_147 -- Get all employees and their departments_147 select_147 employees.department_id,_147 employees.first_name,_147 employees.last_name,_147 departments.department_name_147 from_147 employees_147 join_147 departments on employees.department_id = departments.department_id_147),_147employee_counts as (_147 -- Count how many employees in each department_147 select_147 department_name,_147 count(*) as num_employees_147 from_147 department_employees_147 group by_147 department_name_147)_147select_147 department_name,_147 num_employees_147from_147 employee_counts_147order by_147 department_name;_147```