Portable SQL Types and Vendor-Specific Differences

Learning outcomes

SQL is standardized, but database products implement different type names, ranges, aliases, implicit conversions, temporal semantics, auto-generated keys, and specialized types. Portability means preserving data meaning across those differences—not pretending every engine is identical.

Standard language, product dialects

The SQL standard defines broad concepts, but products evolve independently. A type name that parses in two systems may still differ in range, length semantics, time-zone behavior, collation, or implicit conversion rules.

A practical portable core

Most relational systems support recognizable equivalents for these domains:

The word intent matters. You may express “Boolean” as native BOOLEAN in PostgreSQL, BIT in SQL Server, or a constrained integer in SQLite while preserving the same logical domain.

Major vendor differences at a glance

This table is an orientation, not a migration specification. Confirm the exact version and configuration of each target system.

SQLite: dynamic typing with optional STRICT tables

SQLite values use five storage classes and ordinary columns use affinity. Declaring VARCHAR(50) does not enforce a 50-character limit by itself. STRICT tables strengthen enforcement but intentionally accept only a compact set of declared type names.

CREATE TABLE account (    account_id INTEGER PRIMARY KEY,    display_name TEXT NOT NULL        CHECK (length(display_name) BETWEEN 1 AND 100),    active_flag INTEGER NOT NULL DEFAULT 1        CHECK (active_flag IN (0, 1)),    created_at TEXT NOT NULL        CHECK (datetime(created_at) IS NOT NULL)) STRICT;

The constraints express application meaning that a server DBMS might encode partly through native Boolean, timestamp, and length-constrained character types.

PostgreSQL: rich native domains and strict typing

PostgreSQL provides native Boolean, UUID, JSON/JSONB, arrays, ranges, network-address types, enumerations, domains, and extensive temporal support. These can improve integrity and querying, but they increase migration work when another engine lacks equivalents.

CREATE TABLE account (    account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    public_id UUID NOT NULL UNIQUE,    display_name VARCHAR(100) NOT NULL,    active BOOLEAN NOT NULL DEFAULT TRUE,    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,    preferences JSONB NOT NULL DEFAULT '{}'::jsonb);

MySQL: aliases, SQL modes, and temporal distinctions

MySQL commonly uses AUTO_INCREMENT for generated keys. BOOLEAN is an alias associated with a small integer representation. DATETIME and TIMESTAMP differ in range, storage, conversion, and default behavior. SQL modes influence validation and implicit conversion, so development and production modes must match.

CREATE TABLE account (    account_id BIGINT AUTO_INCREMENT PRIMARY KEY,    display_name VARCHAR(100) NOT NULL,    active BOOLEAN NOT NULL DEFAULT TRUE,    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),    preferences JSON NOT NULL);

SQL Server: Unicode choices and temporal precision

SQL Server uses BIT for Boolean-style values, IDENTITY for many generated numeric keys, NVARCHAR for Unicode text, DATETIME2 for precise date-time values, and DATETIMEOFFSET when an offset is stored.

CREATE TABLE dbo.account (    account_id BIGINT IDENTITY(1,1) PRIMARY KEY,    display_name NVARCHAR(100) NOT NULL,    active BIT NOT NULL CONSTRAINT DF_account_active DEFAULT (1),    created_at DATETIME2(6) NOT NULL        CONSTRAINT DF_account_created DEFAULT (SYSUTCDATETIME()),    preferences NVARCHAR(MAX) NOT NULL        CONSTRAINT CK_account_preferences_json CHECK (ISJSON(preferences) = 1));

Oracle Database: NUMBER, VARCHAR2, DATE, and TIMESTAMP

Oracle commonly uses NUMBER for numeric domains, VARCHAR2 for variable character data, CLOB and BLOB for large values, and DATE for a date-time value that includes time to the second. TIMESTAMP families provide fractional seconds and optional time-zone behavior. Zero-length character strings are treated as NULL in SQL contexts, a major portability difference for applications that distinguish empty from missing text.

CREATE TABLE account (    account_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    display_name VARCHAR2(100 CHAR) NOT NULL,    active_flag NUMBER(1) DEFAULT 1 NOT NULL,    created_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,    preferences CLOB NOT NULL,    CONSTRAINT ck_account_active CHECK (active_flag IN (0, 1)));

Portability architecture

Use layers rather than forcing every engine into its weakest common syntax:

1. Logical model: define domains such as Account ID, Display Name, Active Flag, and Creation Instant.
2. Portable contract: document required range, nullability, precision, uniqueness, and operations.
3. Dialect migrations: map the contract to native declarations and constraints.
4. Application adapter: normalize driver-level differences such as Boolean and timestamp values.
5. Cross-engine tests: run boundary, round-trip, sorting, uniqueness, and invalid-input tests.

Lab: design a portable account contract

Create a vendor-neutral contract before writing migrations:

account_id  purpose: internal identity  range: at least signed 64-bit  nullability: required  uniqueness: primary keyemail  purpose: login/contact identifier  representation: Unicode text, maximum 320 characters  nullability: required  uniqueness: case-normalized business ruleactive  purpose: two-state flag  values: true or false  nullability: requiredcreated_at  purpose: global creation instant  precision: microseconds preferred  nullability: requiredprofile_json  purpose: optional extensible profile attributes  operations: validate JSON and retrieve selected fields

Then implement an SQLite learning version:

CREATE TABLE account_portable (    account_id INTEGER PRIMARY KEY,    email TEXT NOT NULL        CHECK (length(email) BETWEEN 3 AND 320),    email_normalized TEXT NOT NULL UNIQUE,    active INTEGER NOT NULL DEFAULT 1        CHECK (active IN (0, 1)),    created_at TEXT NOT NULL        CHECK (datetime(created_at) IS NOT NULL),    profile_json TEXT        CHECK (profile_json IS NULL OR json_valid(profile_json))) STRICT;INSERT INTO account_portable    (email, email_normalized, active, created_at, profile_json)VALUES    ('Learner@Example.com', 'learner@example.com', 1,     '2026-08-05T12:00:00Z', '{"language":"en"}');

Create a mapping table for each target engine. Record the native type, generation strategy, default expression, JSON validation approach, and known conversion risks.

Common mistakes

Assuming ANSI SQL means identical behavior

Standards reduce differences; they do not eliminate implementation choices, extensions, version gaps, or configuration effects.

Using vendor aliases without documenting the domain

A migration tool can translate syntax only when the intended range, precision, and behavior are explicit.

Choosing the weakest common denominator too early

Native types may provide valuable integrity and performance. Isolate them behind clear contracts and migrations rather than discarding them automatically.

Testing only successful inserts

Portability defects often appear in invalid inputs, overflow, NULL handling, collation, time zones, and round-trip conversion.

Checkpoint and practice

Chapter 2 summary

You can now distinguish tables, rows, columns, schemas, and namespaces; select among major type families; preserve meaning through deliberate type choices; reason about NULL and three-valued logic; and isolate vendor differences behind explicit contracts and tests.

Chapter 3 moves from individual columns to keys, relationships, and integrity rules. Its first lesson will distinguish candidate, primary, alternate, natural, and surrogate keys.

References

• SQLite: Datatypes
• PostgreSQL: Data Types
• MySQL: Data Types
• SQL Server: Data Types
• Oracle Database: Data Types