Naming in PostgreSQL: snake_case, 63 bytes and constraint names Basic

This page answers: why the database should use snake_case, how PostgreSQL handles long and duplicate names, and how to name constraints and indexes.

Case folding: why snake_case

PostgreSQL folds unquoted identifiers to lowercase: Orders, ORDERS and orders are the same table. But "Orders" (in double quotes) is a different name and case-sensitive.

Loading diagram…

-- the ORM creates the table with quoted names
CREATE TABLE "OrderItems" ("orderId" bigint, "createdAt" timestamp);

SELECT orderId FROM OrderItems;
-- ERROR: relation "orderitems" does not exist

SELECT orderId FROM "OrderItems";
-- ERROR: column "orderid" does not exist
-- From now on every hand-written query, dashboard and script must quote every name.

The simplest rule (also the PostgreSQL wiki’s recommendation): use only a-z, 0-9, _, so you never need quotes. Map to camelCase in the ORM/query builder layer:

Tool	How to map
Prisma	@map / @@map in the schema
TypeORM	naming strategy (e.g. a snake_case strategy)
Kysely	CamelCasePlugin
Knex	wrapIdentifier / postProcessResponse hooks

The 63-byte limit

Identifiers are at most NAMEDATALEN - 1 bytes, 63 bytes by default. Longer names are still accepted but truncated, and PostgreSQL only emits a NOTICE:

NOTICE:  identifier "payment_provider_settlement_reconciliation_line_items_amount_cents_idx"
         will be truncated to "payment_provider_settlement_reconciliation_line_items_amount_ce"

Migration tools often swallow NOTICEs. The real consequences (run on PostgreSQL 17):

• Two different names that share the first 63 bytes collide: the second index fails with relation "..._amount_ce" already exists, and the migration fails halfway.
• SQL using the full name is truncated the same way, so it still works; but tools that compare catalog names with the full names in code (schema diff, drift checks) will see a mismatch.

The limit counts bytes, not characters: a letter like ü takes 2 bytes in UTF-8, so names with accented characters fit far fewer than 63 characters. One more reason to stick to ASCII.

Table and column rules

Decision	Suggested default	Reason
Singular or plural tables	Pick one, e.g. plural orders	There is no right answer; mixing is the mistake
Primary key	id	Short, every ORM understands it
Foreign key	<singular table>_id: customer_id	You can tell where a join points just by reading it
Points in time	created_at timestamptz	_at suffix for timestamps; timestamptz stores an absolute instant
Dates without time	birth_date date, due_on date	Distinguishes them from timestamps
Booleans	is_active, has_invoice	Same rule as in code
Money	amount_cents bigint or numeric	Unit in the name, never float
SQL keywords	Avoid user, order, group as table/column names	They must be quoted every time

For real points in time, the PostgreSQL wiki recommends timestamptz over timestamp without time zone (except for abstract timestamps that are not used in arithmetic).

Constraint and index names: know the defaults, then write them out

If you do not name them, PostgreSQL generates names following <table>_<column>_<suffix> (read from the PG 17 source and tested):

Object	Default suffix	Example for table orders
Primary key	pkey (no column)	orders_pkey
Unique	key	orders_external_ref_key
Foreign key	fkey	orders_customer_id_fkey
Check (single column)	check	orders_amount_cents_check
Exclusion	excl	orders_period_excl
Plain index	idx	orders_customer_id_idx

• On a name clash: PG appends a number (orders_customer_id_idx1).
• When too long: PG truncates the table/column part but keeps the suffix.

The default pattern is readable, so use it as your convention and write the names out in migrations. DROP CONSTRAINT / DROP INDEX migrations need stable names that do not depend on how PG truncated or numbered them in that environment.

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY,
  customer_id  bigint NOT NULL,
  amount_cents bigint NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT orders_pkey PRIMARY KEY (id),
  CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT orders_amount_cents_check CHECK (amount_cents >= 0)
);

CREATE INDEX orders_customer_id_created_at_idx ON orders (customer_id, created_at);

Before → after at the database layer

Before	Problem	After
tblOrderInfo	Type prefix, empty Info	orders
"CreatedDate" timestamp	Needs quoting; “Date” but includes time; no time zone	created_at timestamptz
cust (FK column)	Abbreviation, unclear what it references	customer_id

Next: API, events, env.