SQL character types

Navigation

Online convertor (http://www.raditha.com/mysql/mysql2pgsql.php)
Making up the numbers
Basic Administration
Strange characters
Links (http://www.raditha.com/mysql/links.php)
Date and Time

Migration (http://www.raditha.com/postgres/)

While the mysql documentation refers to string types, in postgresql they are known as character types. In the former binary large objects and two other types; enum and set are also lumped together with the string types where as in the latter blobs are treated separately.

mysql seems to take inspiration from Golidloks and the three bears; you find blobs and text fields in three different flavours tiny,medium and large the only difference between them is the upper limit on the data that can be accomodated in a column. Thus for all practical purposes it is safe to use the LARGEBLOB or LARGETEXT types

Just to make things little more complicated you can declare a LONG VARCHAR field in mysql instead of a MEDIUMTEXT, if that is not enough you can use the suffix BINARY on VARCHAR and CHAR fields to make them behave like blobs. This is the database that's supposed to be simple to use. Postgresql is much less confusing unless you want to puzzle over why there exists a CHARACTER VARYING alias for VARCHAR.


Comparision of String Types
mySQL Postgresql
Type Max Length | Type Max Length
char 255b character, char 1GB (approx)
varchar, tinytext 255b varying character, varchar 1GB (approx)
text 64Kb text unlimited
mediumtext 16Mb
longtext 4GB


In pgsql the choice of data type does not effect performance, the same cannot be said about the use of varchar, text and blob in mysql, where they lead to to slower inserts/selects especially with MyIsam tables.

Finally the only difference between blobs and text in mysql is that the former is case sensitive while the latter is not.