I have a ruby on rails application in development that contains an sqlite3 database. In the early development none of the tables in the application had fields that were indexed (other than the ID which was always indexed) and upon deployment to Heroku, the application worked find.
I then altered the three of the tables such that each have one field (besides the ID which is indexed) that has been indexed unique and I still have one table that has not had a field indexed other than the ID. The application works fine in development even with no records in the tables, but when the application is pushed to Heroku with postgres, the application fails on each of the indexed tables, though not on the un-indexed one.
There was no error message when the application was loaded and there was no error when I ran "heroku run rake db:migrate".
The error message in the log is "PG::UndefinedTable: ERROR: relation "[tablename]" does not exist" which looks like the table isn't there. Does anyone have any ideas how I can go about finding the issue and or fixing it?
I have tried completely deleting and rebuilding the database locally, and deleting the application and the database on Heroku several times and re-deploying, to no avail.
I am really at wits end! Any help would be gratefully received!
Thanking you in advance,
You are mixing your use of quoted and non-quoted identifiers, and this is possibly a side-effect of using MySQL in development and PostreSQL in production.
As the docs state ...
Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)
Oracle behaves similarly, and the use of quoted identifiers is generally discouraged as it can lead to just this kind of issue.