PostgreSQL Naming Conventions

Choose one readable naming convention for your project and apply it to databases, tables, sequences, primary keys, constraints, and indexes. PostgreSQL does not require one universal style; consistency helps developers find and maintain objects.

PostgreSQL Naming Conventions

PostgreSQL’s identifier rules are separate from style choices. Uppercase SQL keywords are a formatting convention. For identifiers, lowercase words separated by underscores are a practical default: for example, customer_order or created_at. Use descriptive names and a consistent pattern for tables, columns, sequences, constraints, and indexes. Names such as customer_order_pkey, customer_order_status_check, and customer_order_created_at_idx make an object’s role easier to recognize.

An unquoted identifier is folded to lowercase, so Employee, EMPLOYEE, and employee refer to the same name. A quoted identifier preserves its case: "Employee" is distinct and must be written with the same double quotes and capitalization whenever it is referenced. Prefer lowercase unquoted names for simpler queries and tooling. Avoid reserved words as names; quoting a word does not by itself make it unusable, but it adds a quoting requirement and can make SQL harder to maintain.

Apply the pattern to each object’s role. A table name is usually a singular or plural noun chosen consistently across the schema; column names describe the stored value; a sequence name can identify the table and key it supplies. For constraints and indexes, include the table or column and a suffix such as _pkey, _key, _check, or _idx. These suffixes are conventions, not PostgreSQL requirements. PostgreSQL may generate names for unnamed constraints and indexes, but explicit names make migrations, error messages, and maintenance queries easier to read. Keep names reasonably concise and within PostgreSQL’s identifier-length limit; longer names are truncated, which can make similar names collide.

The following sequence is for an interactive psql session connected to a database where your role can create databases. Run the database creation command first, then switch to it with the psql meta-command. The SQL statements that follow create the sequence, table, primary-key and unique constraints, and secondary index. The final psql commands inspect the database, sequence, and table definitions. PostgreSQL itself is required to run this walkthrough; the commands are not a transcript of a server session.

1. Create a separate demonstration database with CREATE DATABASE naming_demo;.
2. Connect to it with \connect naming_demo.
3. Create the key sequence with CREATE SEQUENCE employee_id_seq;.
4. Create the table with CREATE TABLE employee (employee_id integer DEFAULT nextval('employee_id_seq'::regclass), first_name text NOT NULL, last_name text NOT NULL, email_address text NOT NULL, CONSTRAINT employee_pkey PRIMARY KEY (employee_id), CONSTRAINT employee_email_address_key UNIQUE (email_address));.
5. Make the sequence belong to the key column with ALTER SEQUENCE employee_id_seq OWNED BY employee.employee_id;.
6. Add a separately named index with CREATE INDEX employee_last_name_idx ON employee (last_name);.
7. Inspect the database using \l naming_demo, the sequence using \ds employee_id_seq, and the table using \d employee. The table description lists its columns, constraints, and indexes, including employee_pkey, employee_email_address_key, and employee_last_name_idx.

Quoted names are useful when an existing schema requires mixed case or punctuation, but they create a lasting convention: every reference must preserve the exact spelling and use double quotes. A quoted name that matches a keyword can be used where PostgreSQL permits it, though it is easy for readers to mistake it for SQL syntax. Prefer an alternative such as user_account to avoid that friction. Unquoted lowercase names work naturally with PostgreSQL’s folding behavior and are usually simpler in queries, scripts, and database tools.

This Python snippet formats a small set of example names. It illustrates a naming convention and does not connect to PostgreSQL. Run this example:

objects = ["customer order", "created at", "customer order pkey"]
names = [name.replace(" ", "_") for name in objects]
print("\n".join(names))

customer_order
created_at
customer_order_pkey