Type Mapping Encyclopedia

Type Mapping Encyclopedia — Data Type Mapping Reference

A comprehensive reference for RDBMS-to-RDBMS data type mapping, special type conversions, and character set handling.

MySQL to PostgreSQL Mapping

Special Conversion Patterns

-- MySQL ENUM -> PostgreSQL
-- Method 1: CHECK constraint
CREATE TABLE orders (
    status VARCHAR(20) CHECK (status IN ('pending', 'paid', 'shipped'))
);

-- Method 2: Custom type
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped');
CREATE TABLE orders (status order_status);

-- MySQL AUTO_INCREMENT -> PostgreSQL IDENTITY
-- MySQL:
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY);
-- PostgreSQL:
CREATE TABLE users (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

-- MySQL ON UPDATE CURRENT_TIMESTAMP -> PostgreSQL trigger
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_timestamp BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

Oracle to PostgreSQL Mapping

RDBMS to MongoDB Conversion Patterns

Denormalization Strategy

Relational:                      Document:
+- users -+  +- orders -+      {
| id       |  | id       |        _id: ObjectId,
| name     |  | user_id  |->      name: "John Doe",
| email    |  | total    |        email: "john@test.com",
+----------+  | items[]  |        orders: [
               +----------+          { total: 50000,
                                      items: [
                                        { product: "A", qty: 2 }
                                      ]
                                    }
                                  ]
                                }

Embedding vs. Reference Decision

Character Set / Collation Conversion

MySQL to PostgreSQL

-- MySQL character set check
SHOW VARIABLES LIKE 'character_set%';
SELECT character_set_name, collation_name
FROM information_schema.columns WHERE table_name = 'users';

-- PostgreSQL collation
-- MySQL utf8mb4_general_ci -> PostgreSQL ICU collation
CREATE COLLATION korean_ci (
    provider = icu, locale = 'ko-u-ks-level1', deterministic = false
);

-- Case-insensitive comparison
-- MySQL: utf8mb4_general_ci (default)
-- PostgreSQL: citext extension or LOWER() index
CREATE EXTENSION IF NOT EXISTS citext;
ALTER TABLE users ALTER COLUMN email TYPE citext;

Encoding Conversion Notes

Irreversible Conversion Inventory

Type Mapping Verification Queries

-- Source (MySQL)
SELECT column_name, data_type, column_type,
       character_maximum_length, numeric_precision, numeric_scale,
       is_nullable, column_default, extra
FROM information_schema.columns
WHERE table_schema = 'mydb' AND table_name = 'orders'
ORDER BY ordinal_position;

-- Target (PostgreSQL)
SELECT column_name, data_type, udt_name,
       character_maximum_length, numeric_precision, numeric_scale,
       is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position;