API 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
_141# Postgres SQL Style Guide_141_141## General_141_141- Use lowercase for SQL reserved words to maintain consistency and readability._141- Employ consistent, descriptive identifiers for tables, columns, and other database objects._141- Use white space and indentation to enhance the readability of your code._141- Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`)._141- Include comments for complex logic, using '/* ... */' for block comments and '--' for line comments._141_141## Naming Conventions_141_141- Avoid SQL reserved words and ensure names are unique and under 63 characters._141- Use snake_case for tables and columns._141- Prefer plurals for table names_141- Prefer singular names for columns._141_141## Tables_141_141- Avoid prefixes like 'tbl_' and ensure no table name matches any of its column names._141- Always add an `id` column of type `identity generated always` unless otherwise specified._141- Create all tables in the `public` schema unless otherwise specified._141- Always add the schema to SQL queries for clarity._141- Always add a comment to describe what the table does. The comment can be up to 1024 characters._141_141## Columns_141_141- Use singular names and avoid generic names like 'id'._141- 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_141- Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception._141_141#### Examples:_141_141```sql_141create table books (_141 id bigint generated always as identity primary key,_141 title text not null,_141 author_id bigint references authors (id)_141);_141comment on table books is 'A list of all the books in the library.';_141```_141_141_141## Queries_141_141- When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability_141- Add spaces for readability._141_141Smaller queries:_141_141_141```sql_141select *_141from employees_141where end_date is null;_141_141update employees_141set end_date = '2023-12-31'_141where employee_id = 1001;_141```_141_141Larger queries:_141_141```sql_141select_141 first_name,_141 last_name_141from_141 employees_141where_141 start_date between '2021-01-01' and '2021-12-31'_141and_141 status = 'employed';_141```_141_141_141### Joins and Subqueries_141_141- Format joins and subqueries for clarity, aligning them with related SQL clauses._141- Prefer full table names when referencing tables. This helps for readability._141_141```sql_141select_141 employees.employee_name,_141 departments.department_name_141from_141 employees_141join_141 departments on employees.department_id = departments.department_id_141where_141 employees.start_date > '2022-01-01';_141```_141_141## Aliases_141_141- Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity._141_141```sql_141select count(*) as total_employees_141from employees_141where end_date is null;_141```_141_141_141## Complex queries and CTEs_141_141- If a query is extremely complex, prefer a CTE._141- Make sure the CTE is clear and linear. Prefer readability over performance._141- Add comments to each block._141_141```sql_141with department_employees as (_141 -- Get all employees and their departments_141 select_141 employees.department_id,_141 employees.first_name,_141 employees.last_name,_141 departments.department_name_141 from_141 employees_141 join_141 departments on employees.department_id = departments.department_id_141),_141employee_counts as (_141 -- Count how many employees in each department_141 select_141 department_name,_141 count(*) as num_employees_141 from_141 department_employees_141 group by_141 department_name_141)_141select_141 department_name,_141 num_employees_141from_141 employee_counts_141order by_141 department_name;_141```