When you build an application that works with different kinds of databases, you have to be aware of and deal with many subtle differences between databases. It’s no different when you write an application that works with MySQL and Amazon Redshift.

While developing Integrate.io Sync, we encounter many such differences. In this article, we’d like to share our from-the-trench experience.

Express Your Column with The Quoted Column Name

If you specify a column name without quotes in your query, your column name may have a very limited set of characters: ASCII alphabets, digits and a few other characters.

ALTER TABLE DROP COLUMN my_column;

However, if you quote a column name, you may use a lot more characters. Especially in MySQL, you can even use almost any Unicode character in your column name. (If this is a good practice is another matter…)

MySQL

ALTER TABLE my_table ADD COLUMN `Écoles; 学校\` TEXT; -- valid column name

Amazon Redshift

ALTER TABLE my_table ADD COLUMN “schools (address@domain.edu)” TEXT; -- valid column name

Note that MySQL and Redshift use a different character as a quote character.

GET BACK TO WORK.
We'll handle your ETL pipeline.

Case Sensitive or Case Insensitive

Case matters with MySQL. You can create a column such as “Local Governments”. On the other hand, Amazon Redshift’s column names are not case sensitive. You can use upper case letters in your query, but they get converted to lowercase.

Valid Column Name Characters in MySQL and Amazon Redshift

The following table shows the differences in supported characters between MySQL and Redshift.

Characters MySQL (unquoted) Redshift (unquoted) MySQL (quoted) Redshift (quoted)
ASCII Alphabet YES (case sensitive) YES (case insensitive) YES (case sensitive) YES (case insensitive)
Digit, underscore (_), dollar sign ($)< YES (*1) YES (*2) YES YES
Non-ASCII Characters (Unicode Extended U+0080..U+FFFF) YES NO YES NO
Space ( ) NO NO YES (*3) YES
!#%&’()*+,-./:;<=>?@[]^_~ NO NO YES YES
Double quotes (“) NO NO YES YES (*4)
Grave accent (`) NO NO YES (*5) YES
Backslash (\) NO NO YES (*6) YES

*1) Digit-only column name is not allowed unless quoted.

*2) Column name may not start with a digit or dollar sign ($) unless quoted.

*3) Trailing spaces are not allowed.

*4) Must be escaped with another double quotes (“”)

*5) Must be escaped with another grave accent (``)

*6) Must be escaped with another backslash only if it’s the last character.

*7) MySQL 5.6 and Amazon Redshift as of June 1, 2015