# Can sqlite3 go case-insensitive? **URL:** \ **Category:** Rails\ **Created:** [March 4, 2008, 4:29pm UTC](https://www.ruby-forum.com/t/can-sqlite3-go-case-insensitive/130935 "2008-03-04T16:29:35Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Phlip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ed8c4c/32.png) [@Phlip](https://www.ruby-forum.com/u/Phlip)\ **Post date:** [March 4, 2008, 4:29pm UTC](https://www.ruby-forum.com/t/can-sqlite3-go-case-insensitive/130935/1 "2008-03-04T16:29:35Z")
Railsers: Our tests are getting slow, so among the potential speed boosts we are trying the sqlite3 driver. But someone here (certainly not me!) wrote tests with sloppy case! MySQL doesn’t mind. While I fix these tests, is there some way to make SQLite3 turn case insensitive? (Possibly some way that’s invisible to Google?😉 – Phlip [O'Reilly Media - Technology and Business Training](http://www.oreillynet.com/ruby/blog/2008/03/beast_acts_as_sphinx.html) ---
**Author:** ![Phlip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ed8c4c/32.png) [@Phlip](https://www.ruby-forum.com/u/Phlip)\ **Post date:** [March 6, 2008, 2:35am UTC](https://www.ruby-forum.com/t/can-sqlite3-go-case-insensitive/130935/2 "2008-03-06T02:35:46Z")
What platform? I have had cases where MySQL will go case sensitive, seemingly by just by running on a different platform. I was running an app nicely (not Rails in this case) with Mac OS X Server, but when the data was moved to a Linux machine using a GUI tool it decided to rename all the tables in lower case. (Like you I had nothing to do with the case issue, but was the one who had to fix the problem). I have also had case issues with PostgreSQL in the past as well. Again it was running on Linux. SQL is “generally” case agnostic, but on Linux I’ve seen a number of cases where it isn’t. ---
**Author:** ![Phlip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ed8c4c/32.png) [@Phlip](https://www.ruby-forum.com/u/Phlip)\ **Post date:** [March 6, 2008, 2:42am UTC](https://www.ruby-forum.com/t/can-sqlite3-go-case-insensitive/130935/3 "2008-03-06T02:42:12Z")
Oh, I forgot to mention: Also check your character encoding and collation which may have an effect on case sensitivity. ---
**Author:** ![Phlip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ed8c4c/32.png) [@Phlip](https://www.ruby-forum.com/u/Phlip)\ **Post date:** [March 6, 2008, 4:28am UTC](https://www.ruby-forum.com/t/can-sqlite3-go-case-insensitive/130935/4 "2008-03-06T04:28:20Z")
On Wed, Mar 05, 2008 at 05:34:51PM -0800, Robert W. wrote: > What platform? I have had cases where MySQL will go case sensitive, > seemingly by just by running on a different platform. > > I was running an app nicely (not Rails in this case) with Mac OS X > Server, but when the data was moved to a Linux machine using a GUI > tool it decided to rename all the tables in lower case. (Like you I > had nothing to do with the case issue, but was the one who had to fix > the problem). This is because MySQL stores tables on the filesystem by name (certainly MyISAM tables, and I think InnoDB tables as well). If your DB is on a system with a case-sensitive filesystem (most filesystems Linux supports), MySQL table names are case-sensitive. If your DB is on a case-insensitive (even case-preserving, like HFS+ on MacOS X, though there is a case-sensitive version available) filesystem, however, the files containing the tables will be found even when the case does not match. > I have also had case issues with PostgreSQL in the past as well. Again > it was running on Linux. SQL is “generally” case agnostic, but on > Linux I’ve seen a number of cases where it isn’t. You should not have had any issue with PostgreSQL unless the table names were quoted when they were created. See > **[4.1. Lexical Structure](https://www.postgresql.org/docs/8.3/sql-syntax-lexical.html)** > > 4.1. Lexical Structure 4.1.1. Identifiers and Key Words 4.1.2. Constants 4.1.3. Operators 4.1.4. Special Characters 4.1.5. Comments 4.1.6. Operator Precedence SQL … Note that none of this answers the question about SQLite3. Some googling for it makes me think that it isn’t too promising. It is probably worth your while to fix your case issues anyway, though. Most of the SQL I see generated by ActiveRecord does a lot of table name and column name quoting, which (according to the SQL standard) should enforce case. MySQL is exhibiting non-standard behavior (see above) in this case. (And in so many others. Don’t get me started on how much I loathe MySQL and everything it stands for, despite having had to use it for work for over two years. All my personal projects use PostgreSQL or, occasionally, SQLite.) –Greg ---
**Author:** ![Phlip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ed8c4c/32.png) [@Phlip](https://www.ruby-forum.com/u/Phlip)\ **Post date:** [March 6, 2008, 4:44am UTC](https://www.ruby-forum.com/t/can-sqlite3-go-case-insensitive/130935/5 "2008-03-06T04:44:45Z")
Robert W. wrote: > Oh, I forgot to mention: Also check your character encoding and > collation which may have an effect on case sensitivity. I found COLLATE NOCASE; that leads to this: [http://www.mail-archive.com/removed\_email\_address@domain.invalid/msg29526.html](http://www.mail-archive.com/removed_email_address@domain.invalid/msg29526.html) So the answer is probably to tack a COLLATE NOCASE onto the columns that are indifferent to case. Thanks y’all! – Phlip [O'Reilly Media - Technology and Business Training](http://www.oreillynet.com/ruby/blog/2008/03/beast_acts_as_sphinx.html)