MySQL -> Postgres

Question

Using DB Designer and exporting to SQL I got this as part of the following file. It's in MySQL format, but I need it in Postgres. I trimmed the first 3 lines to work but I'm struggling with the last two (the INDEX lines).

CREATE TABLE applications_interfaces (
  swe_applications_id INTEGER NOT NULL,
  ieo_applications_id INTEGER NOT NULL,
  PRIMARY KEY(swe_applications_id, ieo_applications_id),
  INDEX swe_applications_has_ieo_applications_FKIndex1(swe_applications_id),
  INDEX swe_applications_has_ieo_applications_FKIndex2(ieo_applications_id)
);

My question is what functionality are those two lines providing, and how could it be rewritten to work in Postgres?

@rogerdeuce Unrelated to the question, but could you explain how to set up the formatting for code like that? I saw the snippet tool, but it didn't appear to be quite the same. — Michael, May 29 '15 at 18:29
highlight a code block and push Ctrl + k, for smaller line of code (`like this`) use the back tick (same key as ~ without the shift) — rogerdeuce, May 29 '15 at 18:36

score 2 · Accepted Answer · answered May 29 '15 at 19:07

Turn your code in this:

CREATE TABLE applications_interfaces (
  swe_applications_id INTEGER NOT NULL,
  ieo_applications_id INTEGER NOT NULL,
  PRIMARY KEY(swe_applications_id, ieo_applications_id)
);

CREATE INDEX applications_interfaces_FKIndex1 ON applications_interfaces USING btree (swe_applications_id);
CREATE INDEX applications_interfaces_FKIndex2 ON applications_interfaces USING btree (ieo_applications_id);

Indexes are created after the table. See syntax of create index: http://www.postgresql.org/docs/9.4/static/sql-createindex.html

I suggest to study why you need these indexes. Are they built from you or generated by some tools?
swe_applications_id is already indexed by primary key, so you don't really need first index if you don't have some specific need.

That appears to have worked, thank you. I had looked at the documentation but was struggling to understand if it was actually necessary for the functionality of the table. It appears to mostly just be optimization as best I can tell. — Michael, May 29 '15 at 19:35

score 1 · Answer 2 · edited May 23 '17 at 12:16

While syntactically user_0 helped you, it appears from your comment that you are still not sure what an index does all together.

The way I have gone about understanding indexes is by comparing them to their uses in books.

Indexes are designed to help the reader find information quickly and easily. A complete and truly useful index is not simply a list of the words and phrases used in a publication but an organized map of its contents, including cross-references, grouping of like concepts, and other useful intellectual analysis. Wikipedia

So suppose you need to know more about Primary Keys (PK's) in Mysql and you pick up your trusty Mysql book nearby. You have no idea where in the book PK's are discussed. You would then go to the index at the back of the book. This would point you directly to the right place(s) that PK's are discussed in the book while saving countless amounts of hours perusing through the book.

Indexes in databases work in a very similar way. You can get at the millions of rows of data that you are looking for with the right index in seconds. Also, just like it would take awhile to go through and compile an index in a book, it takes time to create an index on a table. Just like erasing an index from a book takes but a few seconds and the index is forgotten, same goes for dropping an index on a table in a database.

For more info:

Detailed stackoverflow answer on how indexing works.

Wiki Database Indexes.

Indexes in Mysql.

Postgres Indexes

MySQL -> Postgres

2 Answers2