Data lookups

SQL data types

Types side by side across MySQL, SQL Server and PostgreSQL.

All lookups

27 entries.

PurposeMySQL / MariaDBSQL ServerPostgreSQLNotes
Tiny integer TINYINT (−128 to 127) TINYINT (0 to 255) SMALLINT MySQL is signed by default; SQL Server's TINYINT is unsigned
Small integer SMALLINT SMALLINT SMALLINT ±32,767
Integer INT INT INTEGER ±2.1 billion
Big integer BIGINT BIGINT BIGINT For very large counts and IDs
Auto-increment ID INT AUTO_INCREMENT INT IDENTITY(1,1) SERIAL / GENERATED AS IDENTITY SERIAL is legacy in modern PostgreSQL
Exact decimal (money) DECIMAL(10,2) DECIMAL(10,2) / MONEY NUMERIC(10,2) Always use these for currency, never FLOAT
Approximate number FLOAT / DOUBLE REAL / FLOAT REAL / DOUBLE PRECISION Rounding errors — avoid for money
Boolean TINYINT(1) / BOOLEAN BIT BOOLEAN MySQL BOOLEAN is an alias for TINYINT(1)
Fixed text CHAR(n) CHAR / NCHAR(n) CHAR(n) Padded with spaces to length n
Variable text VARCHAR(n) VARCHAR / NVARCHAR(n) VARCHAR(n) Prefix N in SQL Server for Unicode
Long text TEXT / MEDIUMTEXT / LONGTEXT VARCHAR(MAX) / NVARCHAR(MAX) TEXT PostgreSQL TEXT has no practical limit
Binary data BLOB / MEDIUMBLOB VARBINARY(MAX) BYTEA Consider storing files on disk instead
Date DATE DATE DATE No time component
Time TIME TIME TIME
Date and time DATETIME DATETIME2 TIMESTAMP SQL Server DATETIME2 is preferred over DATETIME
Timestamp with zone TIMESTAMP DATETIMEOFFSET TIMESTAMPTZ MySQL TIMESTAMP converts to/from UTC
Year YEAR (use SMALLINT) (use SMALLINT) MySQL-specific
Fixed choices ENUM('a','b') (use CHECK constraint) (use CHECK or a custom TYPE) ENUM is MySQL-specific
Set of choices SET('a','b') (no equivalent) (use an array or join table) MySQL-specific; usually better as a join table
JSON JSON NVARCHAR(MAX) + JSON functions JSON / JSONB JSONB is indexed and faster in PostgreSQL
UUID / GUID CHAR(36) / BINARY(16) UNIQUEIDENTIFIER UUID BINARY(16) saves space in MySQL
IP address VARBINARY(16) / INT UNSIGNED VARCHAR(45) INET / CIDR PostgreSQL has proper network types
Geometry GEOMETRY / POINT GEOGRAPHY / GEOMETRY PostGIS types PostGIS is the most capable
Array (no native type) (no native type) type[] PostgreSQL only
Auto timestamp DEFAULT CURRENT_TIMESTAMP DEFAULT GETDATE() DEFAULT now()
String concatenation CONCAT(a, b) a + b a || b A common migration trip-up
Limit rows LIMIT 10 TOP 10 / OFFSET…FETCH LIMIT 10
Types are broadly equivalent, not identical — check precision and range when migrating between engines.