2

We have our long-used internal db naming conventions. For example: suffix for indexes is "_ix", for foreign key - "_fk".

These conventions are conflicting with default postgresql naming conventions: "_idx" for index and "_fkey" for foreign key.

For example. If i create index and don't define index name explicit like this CREATE INDEX ON a (id); then i'll get such index name: a_id_idx. But according to our conventions, which are used not only for postgresql, there must be a_id_ix.

The best way would be to change autogenerated naming pattern to match our conventions. Seems it should be simple but i can't find how it can be done.

Any solutions?

keddok
  • 531
  • 1
  • 5
  • 15
  • If you want non-default names, set nondefault names. – Craig Ringer Sep 06 '17 at 10:56
  • [I still think it is a bad idea,but] https://www.postgresql.org/docs/9.6/static/sql-alterindex.html `ALTER INDEX [ IF EXISTS ] name RENAME TO new_name;` – wildplasser Sep 06 '17 at 11:00
  • @CraigRinger, there are 2 reasons why i searching ability to change the defaults: 1. There is serial type (quite convenient) generating sequence with name {table_name}_{column_name}_seq and i can't define name explicitly without manual sequence creation 2. It is wrong to forbid using autogeneration at all if there is alternative. But if i can't change the postgres defaults at global level then probably i will have to add some rules in our conventions that prohibit the use of serial type and some other features. – keddok Sep 06 '17 at 11:21
  • You can't change the PostgreSQL name generation. – Craig Ringer Sep 06 '17 at 12:35

1 Answers1

0
CREATE INDEX index_name ON a (id);
Soni Harriz
  • 3,378
  • 1
  • 12
  • 8
  • In some cases i can't define explicit name. For example: for serial column type postgresql generates sequence with name {table_name}_{column_name}_seq, but i need {table_name}_seq. – keddok Sep 06 '17 at 10:18
  • 3
    @keddok: yes you can: `create sequence some_table_seq; create table some_table (id integer default nextval('some_table_seq')); alter sequence some_table_seq owned by some_table.id;` –  Sep 06 '17 at 10:32
  • Thanks, @a_horse_with_no_name. But understand that there are other ways. I don't wanna prohobit the use of serial type, it is quite convenient. I just wanna to change defaults in postgresql. – keddok Sep 06 '17 at 11:00
  • @keddok: then you need to change the Postgres source and compile your own version –  Sep 06 '17 at 11:18