String Types & Platform Traps
Concepts covered: Data Types
The customers table holds full_name, email, country_code, currency_code and billing_address, all text. The 3 families are CHAR(n), fixed width and space-padded; VARCHAR(n), variable width with a declared ceiling; and TEXT, variable width with no declared ceiling. What each does when real data arrives depends on the platform more than on the SQL standard.
CHAR earns its place only for codes
A CHAR(3) column stores 'US' as 'US ' and pads every shorter value to the declared width. PostgreSQL treats the trailing spaces as insignificant in comparisons but keeps them in the stored bytes, MySQL strips them on read, and a value exported to a CSV carries them into whichever system reads it next. That is acceptable only when the value is the same width by definition. ISO 3166-1 alpha-2 country codes are CHAR(2), ISO 4217 currency codes are CHAR(3), and a width that matches the standard rejects 'USA' in a country column before it reaches a join.
What each platform does past the limit
| Platform | Insert longer than VARCHAR(n) | VARCHAR(n) versus TEXT |
|---|---|---|
| PostgreSQL | Error: value too long for type character varying(n) | Identical storage and speed; the limit is validation only |
| MySQL 5.7+ | Error in strict mode; truncated with a warning in non-strict mode | Same InnoDB storage for long values; TEXT is indexable only by prefix and cannot take a literal DEFAULT before 8.0.13 |
| Snowflake | Error; the default VARCHAR ceiling is 16,777,216 characters | No storage penalty for a large declared ceiling |
| BigQuery | Error when declared STRING(L); plain STRING has no limit | STRING is the only type; L is a check, not a storage hint |
| Oracle | Error; VARCHAR2(100 BYTE) and VARCHAR2(100 CHAR) count differently | Default semantics are bytes unless NLS_LENGTH_SEMANTICS says CHAR |
The MySQL row is the one that loses data. Without STRICT_TRANS_TABLES in sql_mode, a 300-character address inserted into VARCHAR(255) becomes a 255-character address and a warning the driver never surfaces. Strict mode has been the default since 5.7, but a server configured in 2014 and upgraded in place can still lack it, so the first check on an inherited MySQL instance is SELECT @@sql_mode.
Characters, bytes and the number 191
VARCHAR(n) counts characters everywhere except Oracle in byte mode, but indexes count bytes. Under MySQL's utf8mb4 a character takes up to 4 bytes, and InnoDB's older COMPACT row format caps an index key at 767 bytes. Dividing 767 by 4 gives 191, which is why Laravel migrations defaulted string columns to 191 characters for years and why email VARCHAR(255) with a unique index fails to create on those tables. The DYNAMIC row format, default since MySQL 5.7.9, raises the cap to 3,072 bytes for new tables.
Sizing against the data that will arrive
A length chosen from the first 100 rows of sample data is the common failure. The rule that survives: a code gets CHAR of the standard's width, text a human types gets a VARCHAR whose ceiling comes from a published limit or a generous round number, and free text that can grow gets TEXT. Email addresses have a ceiling of 254 characters under RFC 5321, so email VARCHAR(254). Personal names have no documented maximum, and a 40-character limit rejects Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr., a real 53-character name, so full_name VARCHAR(255). Addresses run past 200 characters in Japan and Brazil, so billing_address TEXT.
Some values look numeric and are text. A US postal code 02134 loses its leading zero in an INT column. A phone number in E.164 form is a plus sign and up to 15 digits, and the plus sign is data. Account numbers, order references and tax ids are never summed, so each is a string sized to the issuing system's format. The test is arithmetic: a value never added, subtracted or compared as a magnitude is text.
The customers.email column runs on PostgreSQL. Which declaration fits the data it will hold?
- VARCHAR(50), since the sample shows 95% of addresses are shorter than that
- VARCHAR(254) with a CHECK that the value contains '@'
- CHAR(254), so every row is the same width
- TEXT with no constraint, because PostgreSQL stores it the same way
About this lesson
This section is part of the Schema Types lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.