Database Reference
In-Depth Information
There's More
The standard names for indexes in PostgreSQL are:
{tablename}_{columnname(s)}_{suffix}
where the suffix is one of the following:
F
pkey for a Primary Key constraint
F
key for a Unique constraint
F
excl for an Exclusion constraint
F
idx for any other kind of index
Standard suffix for sequences is
F
seq for all sequences
Tables can have multiple triggers fired on each event. Triggers are executed in alphabetical
order, so trigger names should have some kind of action name to differentiate them and to
allow the order to be specified. It might seem a good idea to put
INSERT
,
UPDATE
, or
DELETE
in the trigger name, though that can get confusing if you have triggers that work on both
UPDATE
and
DELETE
, and may end up as a mess.
A useful naming convention for triggers is:
{tablename}_{actionname}_{after|before}__trig
If you do find yourself with strange and/or irregular object names, it will be a good idea to use
the
RENAME
subcommands to get things tidy again. Examples are:
ALTER INDEX badly_named_index RENAME TO tablename_status_idx;
Handling objects with quoted names
PostgreSQL object names can contain spaces and mixed case characters if we enclose the
tablenames in double quotes. This can cause some difficulties, so this recipe is designed to
help you if you get stuck with this kind of problem.
Case sensitivity issues can often be a problem for people more used to working with other
database systems, such as MySQL, or for people who are facing the challenge of migrating
code away from MySQL.
