PostgreSQL Table Naming Conventions Guide

August 21, 2026 by Chat2DB

Naming conventions sound like bikeshedding until you inherit a database where half the tables are "UserAccounts", one is order (quoted, everywhere), and two indexes silently share a truncated name. Then they become the difference between queries you can type from memory and queries you copy-paste out of fear. PostgreSQL has specific, sometimes surprising rules about identifiers — case folding, a 63-byte length limit, reserved words — and the community conventions exist precisely to keep you out of those sharp corners.

This guide covers the conventions that have broad consensus, presents the genuinely contested ones (singular vs plural) honestly, and shows the actual error messages you hit when you break the rules.

Use Lowercase snake_case — Here's Why It's Not Just Style

The single most important rule: name every table and column in lowercase, with underscores between words. user_accounts, order_items, created_at. This is not an aesthetic preference; it follows from how PostgreSQL parses identifiers.

Unquoted identifiers are folded to lowercase. SELECT * FROM UserAccounts is, to Postgres, exactly select * from useraccounts. (The SQL standard says fold to uppercase; Postgres folds down — one more reason not to rely on case at all.) Quoted identifiers, on the other hand, preserve case exactly. Mix the two and you get the classic trap:

CREATE TABLE "UserAccounts" (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
 
SELECT * FROM UserAccounts;

ERROR:  relation "useraccounts" does not exist
LINE 1: SELECT * FROM UserAccounts;
                      ^

The table exists — but it is named UserAccounts with a capital U and A, and your unquoted reference folded to useraccounts, which does not. From the moment you create a CamelCase table, every query, every ORM configuration, every psql session, every hand-written report must quote it as "UserAccounts", forever. Miss the quotes once and you get the error above; worse, if someone later creates an unquoted useraccounts, both tables coexist and queries hit different ones depending on quoting.

With lowercase snake_case names, quoting is never necessary, case never matters, and what you type is what is stored in the catalog. This is why essentially every Postgres style guide, and the default behavior of migration tools like those in Rails, Django, and Ecto, lands on snake_case.

Singular vs Plural Table Names

This is the one convention with two legitimate camps, and it is worth presenting both fairly.

The plural camp (users, orders, order_items) argues that a table is a collection of rows, so the name should describe the set: SELECT * FROM users reads as "from the users." Rails popularized this, and much of the web-application world follows it, so it is often the path of least resistance with ORMs.

The singular camp (user, order, order_item) argues that the table names the entity type, that singular reads better in joins and column references (user.email vs users.email), and that it sidesteps irregular plurals — nobody enjoys deciding between person/people, statuses, or criteria. Data-warehouse and academic modeling traditions lean singular. Note, though, that singular walks straight into more reserved-word collisions: user and order are exactly the words you cannot use unquoted, which is a practical argument for plural in Postgres specifically.

The honest recommendation: the choice matters far less than the consistency. Pick one, write it down in your team's conventions doc, and enforce it in code review. A database that is 100% plural or 100% singular is easy to work with; one that is 60/40 forces a catalog lookup before every query.

Naming Join Tables

For many-to-many link tables, the dominant convention is to concatenate the two table names in alphabetical order: students + courses becomes courses_students or, more readably, keep them as-is when one order is clearly more natural. Two patterns work well:

-- Pattern 1: both table names, alphabetical
CREATE TABLE courses_students (
    course_id  bigint NOT NULL REFERENCES courses (id),
    student_id bigint NOT NULL REFERENCES students (id),
    PRIMARY KEY (course_id, student_id)
);
 
-- Pattern 2: name the relationship itself
CREATE TABLE enrollments (
    course_id   bigint NOT NULL REFERENCES courses (id),
    student_id  bigint NOT NULL REFERENCES students (id),
    enrolled_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (course_id, student_id)
);

Prefer pattern 2 whenever the relationship has a domain name and especially when it carries its own attributes. enrollments, memberships, permissions_grants — the moment a join table grows a created_at or a role column, it is an entity, and it deserves an entity's name. Reserve mechanical concatenation for pure link tables with no attributes.

Primary Key Columns: id or user_id?

Two conventions coexist. The short form names every primary key id (users.id, orders.id); the long form repeats the table (users.user_id). The short form is more common and pairs with foreign keys named <referenced_table_singular>_id:

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id)
);

This gives you the pleasant property that a foreign key's name tells you its target: customer_id points at customers.id. The long form's advantage is that USING (customer_id) join syntax works and columns stay unambiguous in multi-table queries without aliases. Either is defensible; again, consistency wins. Whichever you pick, always name foreign key columns after the referenced table plus _id (or a role prefix when a table references the same parent twice: sender_id, recipient_id, both referencing users).

Index and Constraint Names: What Postgres Generates

If you do not name constraints and indexes, Postgres generates names for you, and knowing the patterns lets you read any psql table description instantly:

users_pkey — primary key
users_email_key — unique constraint on email
users_email_idx — plain index from CREATE INDEX without a name
orders_customer_id_fkey — foreign key on customer_id
orders_total_cents_check — check constraint

\d orders
...
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "orders_total_cents_check" CHECK (total_cents >= 0)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

The auto-generated names are good — arguably better than most hand-written ones — so the modern advice is: let Postgres name simple constraints, and name explicitly when (a) the name will appear in application-facing error handling, e.g. an upsert with ON CONFLICT ON CONSTRAINT, or code that maps constraint violations to user messages; (b) the constraint is complex enough that the default name is meaningless, like a multi-column check; or (c) your migration tool diffs schemas by name and you want stability across environments. When you do name explicitly, mimic the built-in suffixes (_pkey, _key, _idx, _fkey, _check, _excl) so mixed schemas still read uniformly.

The 63-Byte Identifier Limit

PostgreSQL identifiers are limited to NAMEDATALEN - 1 = 63 bytes (not characters — multibyte UTF-8 names hit the wall sooner). Longer names are not an error; they are silently truncated with only a notice:

CREATE INDEX idx_customer_subscription_billing_address_normalized_country_region_code
    ON billing_addresses (country_code, region_code);

NOTICE:  identifier "idx_customer_subscription_billing_address_normalized_country_region_code"
         will be truncated to "idx_customer_subscription_billing_address_normalized_country_r"

The danger is collision: two long names that differ only after byte 63 truncate to the same identifier, and the second CREATE fails with "already exists" — or worse, your migration tool thinks an object exists when it does not. Auto-generated names on tables with long names and multi-column constraints get there faster than you expect. Keep table names comfortably short so that <table>_<columns>_<suffix> still fits, and if a generated name would exceed the limit, assign an explicit shorter one in the migration.

Reserved Words: user, order, and Friends

Some of the most natural English table names are SQL keywords. The two that bite everyone:

CREATE TABLE user (id bigint PRIMARY KEY);

ERROR:  syntax error at or near "user"
LINE 1: CREATE TABLE user (id bigint PRIMARY KEY);
                     ^

user is a reserved word (it is a function-like expression returning the current user), and order fails the same way because of ORDER BY. The quoting workaround exists — CREATE TABLE "user" (...) — but it condemns you to quoting in every statement for the life of the schema, exactly the trap described earlier. The better fix is to sidestep: users (a point for the plural camp), or app_user, orders, purchase_order. Other frequent offenders worth avoiding unquoted: group, check, default, desc, limit, offset, references, column, table. When in doubt, the catalog knows:

SELECT word, catcode FROM pg_get_keywords() WHERE word IN ('user', 'order', 'group');
 
 word  | catcode
-------+---------
 group | R
 order | R
 user  | R

R means fully reserved. Grepping a candidate name against pg_get_keywords() before a migration is a thirty-second insurance policy. Schema-aware clients help here too — Chat2DB, a free AI database client (https://chat2db.ai/download (opens in a new tab), or the web version at https://app.chat2db.ai (opens in a new tab)), flags keyword collisions in its SQL editor and quotes generated DDL only when actually required, which keeps accidental "order"-style tables out of your schema in the first place.

Schema Naming

Everything above applies to schema names too: lowercase snake_case, short, no keywords. Use schemas as namespaces for genuinely separate concerns — billing, analytics, audit — rather than as a junk drawer. Avoid the pg_ prefix (reserved for system schemas) and be careful about building on public implicitly: name the schema explicitly in DDL (CREATE TABLE billing.invoices ...) or set search_path deliberately, so the same migration behaves identically everywhere. If you multi-tenant by schema, keep tenant schema names machine-generated and boring (tenant_8421), never derived from user input, both for the 63-byte limit and for injection safety.

Name for Stability, Not for Today's Meaning

A last principle that pays off over years: names outlive the code that created them, and renaming a table in a live system is expensive — every query, view, function, FDW, replication config, and downstream consumer references it. So:

Avoid embedding volatile facts in names: users_v2, orders_new, temp_customers all become permanent lies. If you need a rework, rename in a planned migration and drop the old name, or use a view as a compatibility shim during the transition.
Avoid team or project names that reorganize away, and type prefixes like tbl_ that encode nothing the catalog does not already know.
Prefer names that describe the data, not the current feature consuming it: payment_attempts ages better than checkout_v3_events.
Keep constraint and index names derivable from table plus columns, so migrations can be generated and diffed mechanically across environments.

Summary

The convention set with the best cost-benefit ratio in PostgreSQL: lowercase snake_case everywhere, no quoted identifiers ever; pick singular or plural and never mix (plural dodges user and order); name join tables after the relationship when it has one; id primary keys with <table>_id foreign keys; follow the built-in _pkey/_key/_idx/_fkey suffixes when naming constraints yourself; stay well under 63 bytes; check pg_get_keywords() before christening anything; and choose names you will not need to change. None of these rules is enforced by the database — which is exactly why writing them down and enforcing them in review is what separates schemas that are a pleasure to query from the ones you approach with a quoting cheat sheet.

Postgres Unique Constraint vs Unique Index psycopg2 vs psycopg3: Differences and Migration