SQL character types
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.
