Choosing Column Types: VARCHAR vs TEXT, TIMESTAMPTZ, NUMERIC vs FLOAT

Use TEXT or an unbounded string unless a length is a real business rule, store instants as timestamps with time zone (or UTC in engines that lack them), keep money in NUMERIC or integer cents and never in floating point, and reach for a lookup table before an enum type. The right answer differs by engine in the details, and those details are what your schema diagram will show back to you.

Strings: VARCHAR, TEXT and friends#

In PostgreSQL there is no performance difference between varchar(n), varchar and text; the length is just a check constraint. Use text unless the limit means something (an ISO country code is char(2), a name is not "50 characters"). In MySQL and MariaDB the choice matters more: VARCHAR is stored in the row and counts toward the row size limit, while TEXT types are stored partly off-page, cannot take a plain default value in older versions and need a prefix length to be indexed. SQL Server has varchar(n) up to 8000 bytes and varchar(max) beyond, with nvarchar for UTF-16 text; Oracle has VARCHAR2 up to 4000 bytes by default and CLOB for long text. SQLite has type affinity rather than types, so VARCHAR(50) is stored as TEXT without any length enforcement.

Whatever you choose, be consistent, and decide on character set and collation once. In MySQL that means utf8mb4, which is the default in current versions and the only way to store every Unicode character.

Timestamps and time zones#

The mistake that costs the most is storing wall-clock time without saying which clock. PostgreSQL's timestamptz stores an instant in UTC and converts to the session time zone on the way in and out; plain timestamp stores whatever digits you gave it. Use timestamptz for anything that happened, and timestamp or date only for things that are genuinely local, like a store's opening hours.

MySQL splits this differently: TIMESTAMP is converted to UTC for storage and back to the session zone on read, but its range ends in 2038; DATETIME stores the digits unchanged with a much wider range. Many teams therefore use DATETIME and store UTC by convention. SQL Server offers datetime2 (no zone) and datetimeoffset (keeps the offset); avoid the legacy datetime with its 3-millisecond rounding. Oracle's DATE already includes the time to the second, and TIMESTAMP WITH TIME ZONE or WITH LOCAL TIME ZONE add zone handling. SQLite has no temporal type at all; store ISO-8601 text or Unix seconds and pick one for the whole schema.

-- PostgreSQL
created_at timestamptz NOT NULL DEFAULT now()

-- MySQL
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

-- SQL Server
created_at datetime2(6) NOT NULL CONSTRAINT df_created DEFAULT sysutcdatetime()

Money and other exact numbers#

Floating point cannot represent 0.10 exactly, so FLOAT and DOUBLE are wrong for money, tax rates, quantities on invoices and anything that is added up and compared. Use NUMERIC(p, s) (spelled DECIMAL in MySQL and NUMBER(p, s) in Oracle) or store integer minor units such as cents in a BIGINT. The integer approach is simplest across engines and is what many payment systems do. PostgreSQL's money type and SQL Server's money exist but bring locale and rounding surprises; skip them. SQLite has no exact decimal type, which is one more reason to store cents as integers if SQLite is in your stack.

Integers, identity and auto-increment#

Pick the width for the lifetime of the table: INT holds about 2.1 billion, BIGINT about 9.2 quintillion. Primary keys on tables that grow should be BIGINT from day one; changing the type later is a rewrite of the table and every foreign key. Generated keys are spelled differently everywhere: GENERATED ALWAYS AS IDENTITY or bigserial in PostgreSQL, AUTO_INCREMENT in MySQL and MariaDB, IDENTITY(1,1) in SQL Server, identity columns or sequences in Oracle, and an INTEGER PRIMARY KEY rowid alias in SQLite. The trade-offs between generated integers and UUIDs are a topic of their own, covered in primary keys: serial vs UUID vs ULID.

Booleans#

PostgreSQL has a real boolean. MySQL and MariaDB accept BOOLEAN but store TINYINT(1), and that is what introspection reports. SQL Server uses bit. Oracle added a SQL BOOLEAN type in its 23 release; on older versions teams use NUMBER(1) or CHAR(1) with a check constraint. SQLite stores 0 and 1 in an integer column. If a schema has to be portable, name boolean columns clearly (is_active, has_paid) so the intent survives the type mapping.

JSON columns#

PostgreSQL's jsonb is the strongest option: binary storage, operators, and GIN indexes over the document. MySQL has a native binary JSON type with path functions and generated columns for indexing. MariaDB's JSON is an alias for LONGTEXT with a validity check, which is fine for storage but not equivalent. SQL Server has long stored JSON in nvarchar(max) with ISJSON, JSON_VALUE and OPENJSON; check your version for a dedicated type. Oracle has a native JSON type in recent releases and IS JSON constraints on text and LOB columns before that. SQLite ships JSON functions and stores documents as text.

Use JSON for genuinely semi-structured data: payloads you do not query by field, settings blobs, third-party responses. If you find yourself indexing three keys inside the document, they want to be columns.

Enums versus lookup tables#

PostgreSQL enum types and MySQL ENUM columns are compact and self-documenting, but changing them is awkward: PostgreSQL can add values but not remove or reorder them easily, and MySQL needs an ALTER TABLE that rewrites the column definition. A lookup table with a foreign key is portable to every engine, can carry extra attributes (a display name, a sort order, an active flag) and shows up in an ER diagram as a relationship you can see. The PetClinic sample schema does exactly that with its types table for pet types. A CHECK (status IN ('NEW', 'PAID', 'SHIPPED')) constraint is the lightweight middle ground when the set is small and stable.

What introspection reports, and why your diagram says int4#

The type you write is not always the type the engine stores, and metadata reports the stored one. A tool that draws a schema by reading the catalog, rather than by parsing your SQL, therefore shows the engine's names.

Oracle also folds unquoted identifiers to upper case, so the same migration reports OWNERS.FIRST_NAME where PostgreSQL reports owners.first_name; see why Oracle shows your tables in uppercase. None of this is a bug in your migration. It is the engine telling you what it actually built, which is the most useful thing a diagram can do.

Where SchemaDrawer fits#

SchemaDrawer draws that catalog view directly from your migration files: it runs them in Docker against a throw-away database of the engine and version you choose, reads the tables, columns, types, primary keys and foreign keys over JDBC, and returns an SVG diagram. Because nothing is parsed, the column types in the diagram are the ones the engine reports, int4 and timestamptz on PostgreSQL, INT and DATETIME on MySQL, NUMBER and VARCHAR2 on Oracle. Running the same migrations against two engines is the fastest way to see how a type decision lands on each. Details of the supported engines are on the databases page.

Key takeaways#

• Use unbounded text unless a length is a rule; use utf8mb4 on MySQL.
• Store instants with a time zone, or as UTC by convention where the engine cannot.
• Money is NUMERIC or integer cents, never FLOAT.
• Lookup tables beat enum types for anything that changes and anything that should appear in a diagram.
• Expect the catalog's type names, not yours, in any tool that introspects the database.