This cheat sheet provides a clear guide to PostgreSQL's default object naming conventions, which ensure consistency, clarity, and maintainability in database schemas. Our team enforces these defaults across all tables, indexes, sequences, and constraints to maintain uniformity.

postgresql-naming-convention-cheat-sheet.md

PostgreSQL Naming Conventions Cheat Sheet

This cheat sheet provides a clear guide to PostgreSQL's default object naming conventions, which ensure consistency, clarity, and maintainability in database schemas. Our team enforces these defaults across all tables, indexes, sequences, and constraints to maintain uniformity.

1. Default Naming Pattern

{table}_{column(s)}_{suffix}

PostgreSQL automatically generates names following this structure when no explicit name is provided.

Note: We adhere strictly to PostgreSQL defaults; custom naming conventions are not used as they may introduce:

• Team confusion: Engineers may interpret rules differently.
• Migration issues: Tools and ORMs often assume standard suffixes.
• Inconsistent documentation: Makes debugging and reading logs harder.
• Tooling conflicts: Some PostgreSQL extensions expect standard names (_pkey, _seq).

2. Standard Suffixes

Suffix	Object Type
pkey	Primary key constraint
key	Unique constraint
excl	Exclusion constraint
idx	Non-unique index
fkey	Foreign key constraint
check	Check constraint
not_null	NOT NULL constraint (PostgreSQL 18+)
seq	Sequence

3. Examples of Auto-Generated Names

Object	Default Name
Foreign Key	order_user_id_fkey
Index	session_expires_at_idx
Primary Key	user_pkey
Unique	user_email_key

4. Best Practices

All constraints and indexes should be explicitly named in migrations to ensure consistency across environments. Relying solely on auto-generated names can lead to issues such as brittle migrations that break when schema changes occur, inconsistencies between development, staging, and production databases, and difficulties when debugging or analyzing logs. Explicit naming also improves readability, makes schema reviews easier, and helps maintain clear documentation. It is recommended to follow the PostgreSQL default suffixes for all objects, including primary keys, foreign keys, unique constraints, indexes, and sequences, and to enforce singular table names for clarity and consistency.

Example:

ALTER TABLE user ADD CONSTRAINT user_email_key UNIQUE (email);

5. Singular Table Names Rule

For clarity and consistency, we enforce the use of singular nouns for all table names in our database schemas.

Example:

user
order
product
invoice

Why Singular Names?

1. Clarity: Each table row represents a single entity.

2. Convenience: Singular names are simpler and consistent. Irregular plurals or uncountable nouns (e.g., News) complicate naming.

   customer
   order
   user
   status
   news
   

3. Aesthetic and Logical Order: Particularly in master-detail scenarios, singular names read naturally:

   Master: order
   Detail: order_detail
   

4. Simplicity: Using singular names across tables, keys, relationships, and classes ensures consistency and reduces cognitive load.

   Table: customer
   PK: customer_id
   Table: customer_address
   Class: customer
   SQL: SELECT * FROM customer WHERE customer_id = 100
   

5. Globalization: Singular names are easier for multinational teams to understand and reduce errors with irregular plurals.

6. Efficiency: Shorter names save typing, disk space, and make schemas easier to read.

⚠️ PostgreSQL does not enforce singular vs plural — this is a design convention.

6. Attribute Naming Conventions

In the table namespace strategy, column names only describe the property and do not include the name of the entity they belong to. This approach aligns more closely with object-oriented design and results in shorter, more readable column names. When referencing a column in SQL queries, the table name must be used to clarify which table the column belongs to.

Guidelines:

• Primary key columns should be named id.
• Foreign keys should indicate the referenced table using the table name, e.g., user_id in an order table referencing user.
• For tables with multiple relationships to the same entity, use descriptive names for foreign keys, e.g., buyer_id and seller_id in a mapping table referencing user.
• Other columns should be named concisely and clearly, avoiding unnecessary table prefixes unless needed for clarity.

Example:

Primary Key: id (in user table)
Primary Key: id (in order table)
Foreign Key: user_id (in order table)
Other Column: order_date

This default strategy reduces redundancy, simplifies application code, and ensures clarity when performing joins or referencing columns in queries.

7. Additional Resources

• PostgreSQL Documentation: ALTER TABLE
• StackOverflow: Default constraint names