Generating multiple indexes in a single postgres table

Question

I have some source syntax that I need to convert from MySQL to postgreSQL. It seems that there is no analog in postgres for generating multiple indexes on a single table. Also, I am having trouble understanding why one would generate multiple indexes on a single table:

ALTER TABLE concept 
  ADD PRIMARY KEY (table_id), 
  ADD UNIQUE INDEX idx_table_table_id (table_id), 
  ADD INDEX idx_table_code (table_code), 
  ADD INDEX idx_table_v_id (v_id), 
  ADD INDEX idx_cd_id (d_id), 
  ADD INDEX idx_table_class_id (table_class_id);

any ideas?

Answer 1

In PostgreSQL each constraint is added separately. See ALTER TABLE.

Your single DDL (Data Definition Language) statement needs to be broken down into:

• Constraints keep data in check according to rules:

   ALTER TABLE concept ADD PRIMARY KEY (table_id);
  

• Indexes are useful to speed up some queries. Use carefully:

   CREATE INDEX idx_table_code ON concept (table_code);
  
   CREATE INDEX idx_table_v_id ON concept (v_id);
  
   CREATE INDEX idx_cd_id ON concept (d_id);
  
   CREATE INDEX idx_table_class_id ON concept (table_class_id);
  

  Note: Create indexes only when you need them. My [very personal] rule of thumb is not to exceed 10 for each table. If I get to that level, then I need to reconsider carefully what I'm doing.