The standard names for indexes in PostgreSQL are as follows:
{tablename}_{columnname(s)}_{suffix}
Here, the suffix is one of the following:
- pkey: This is used for a primary key constraint
- key: This is used for a unique constraint
- excl: This is used for an exclusion constraint
- idx: This is used for any other kind of index
The standard suffix for all sequences is seq.
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, but that can get confusing if you have triggers that work on both UPDATE and DELETE, and all of this may end up as a mess.
The alphabetical order for trigger names always follows the C locale, regardless of your actual locale settings. If your trigger names use non-ASCII characters, then the actual ordering might not be what you expect.
The following example shows how the characters è and é are ordered in the C locale. You can change the locale and/or the list of strings to explore how different locales affect ordering:
WITH a(x) AS (
VALUES ('è'),('é')
) SELECT *
FROM a
ORDER BY x
COLLATE "C";
A useful naming convention for triggers is as follows:
{tablename}_{actionname}_{after|before}_trig
If you do find yourself with strange or irregular object names, it will be a good idea to use the RENAME subcommands to get things tidy again. Here is an example of this:
ALTER INDEX badly_named_index RENAME TO tablename_status_idx;