Introduction

As database systems, both MySQL and PostgreSQL offer a variety of data types, each with its unique properties and constraints.

This article compares the data types between MySQL and PostgreSQL. Here is a comparison table.

MySQL（Capacity）	PostgreSQL（Capacity）
TINYINT (1B)	-
SMALLINT (2B)	SMALLINT (2B)
INT (4B)	INT (4B)
BIGINT (8B)	BIGINT (8B)
FLOAT (4B)	NUMERIC (4B)
DATE	DATE
DATETIME	-
TIME	TIME
TIMESTAMP	TIMESTAMP
CHAR (255B)	CHAR (1GB)
VARCHAR (65KB)	VARCHAR (1GB)
TINYTEXT (255B)	-
TEXT (65KB)	TEXT (1GB)
MEDIUMTEXT (16MB)	-
LONGTEXT (4GB)	-

Numeric Data Type Mapping

I will introduce the numerical data type mappings between MySQL and PostgreSQL.

TINYINT in MySQL

In MySQL, TINYINT is a small integer data type. The size is one byte, which allows a range of -128 to 127 for signed values or 0 to 255 for unsigned values. In contrast, PostgreSQL does not have a direct equivalent for TINYINT. However, it's often approximated using the SMALLINT type or the BOOLEAN type, depending on the context.

SMALLINT in MySQL and PostgreSQL

Both MySQL and PostgreSQL have the SMALLINT type. In MySQL, a SMALLINT is a two-byte integer. For unsigned values, it can store numbers from 0 to 65535. For signed values (the default), the range is -32768 to 32767. PostgreSQL's SMALLINT type aligns perfectly with MySQL's; it uses two bytes of storage and supports the same range of values.

INT in MySQL and PostgreSQL

INT is a commonly used integer data type in both MySQL and PostgreSQL. It uses four bytes of storage. The range of values for an INT in MySQL is from -2147483648 to 2147483647 for signed values, or from 0 to 4294967295 for unsigned values. PostgreSQL does not have unsigned integer types, but its INT type matches the range of a signed INT in MySQL, providing compatibility for most common use-cases.

BIGINT in MySQL and PostgreSQL

BIGINT is the largest integer type in both MySQL and PostgreSQL. It uses eight bytes of storage. The range for a signed BIGINT in MySQL is -9223372036854775808 to 9223372036854775807. For unsigned BIGINT values, the range is 0 to 18446744073709551615. As with the INT type, PostgreSQL's BIGINT type has the same range as a signed BIGINT in MySQL.

FLOAT in MySQL and NUMERIC in PostgreSQL

MySQL's FLOAT is a four-byte floating-point number. It can store decimal values, and the precise range depends on the specific value. PostgreSQL's NUMERIC type, on the other hand, can precisely store numbers with a large number of digits. It varies in size but can comfortably handle the kind of data typically stored in a MySQL FLOAT.

Date and Time Data Type Mapping

The representation of date and time is a critical aspect in databases as they are used in various applications such as logging events, tracking activities, and time series analysis. Here I will look at how MySQL and PostgreSQL handle the mapping of these data types.

DATE in MySQL and PostgreSQL

In both MySQL and PostgreSQL, the DATE data type is used to store the date. It stores the year, month, and day, with a format of YYYY-MM-DD. In MySQL, the range is '1000-01-01' to '9999-12-31', while in PostgreSQL, the range is from 4713 BC to 5874897 AD, which provides a wider span.

DATETIME in MySQL

DATETIME is a data type in MySQL used to store the date and time. It has a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. This data type is highly useful for storing precise points in time. PostgreSQL does not have an exact equivalent for the DATETIME data type; instead, it uses TIMESTAMP without time zone for similar purposes.

TIME in MySQL and PostgreSQL

The TIME data type is used to represent the time of the day in both MySQL and PostgreSQL. In MySQL, TIME can represent a duration or time of the day, ranging from '-838:59:59' to '838:59:59'. In PostgreSQL, TIME can store times of the day without a timezone, and it's range is '00:00:00' to '24:00:00'.

TIMESTAMP in MySQL and PostgreSQL

Both MySQL and PostgreSQL support the TIMESTAMP data type. In MySQL, TIMESTAMP is used to store a date and time pair. It ranges from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. In contrast, PostgreSQL's TIMESTAMP can hold dates and times from 4713 BC to 294276 AD. PostgreSQL offers more flexibility, as it supports both TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE.

String Data Type Mapping

Storing and manipulating string data is a common task in databases.

CHAR in MySQL and PostgreSQL

In MySQL, the CHAR data type is used to store character strings with a fixed length. The length can be specified in bytes and it ranges from 0 to 255 bytes. PostgreSQL's CHAR, also known as CHARACTER, behaves similarly but with one key difference - the maximum length in PostgreSQL is considerably larger, at 1GB.

VARCHAR in MySQL and PostgreSQL

The VARCHAR data type in MySQL and PostgreSQL is used to store variable-length character strings. MySQL's VARCHAR can store up to 65,535 bytes of data, while PostgreSQL's VARCHAR (or CHARACTER VARYING) can store up to 1GB.

TINYTEXT in MySQL

TINYTEXT is a MySQL-specific type used for storing variable-length strings. It can store up to 255 bytes of data. There is no direct equivalent of this type in PostgreSQL, but its use cases can be covered by TEXT.

TEXT in MySQL and PostgreSQL

In MySQL, the TEXT data type is used for storing variable-length strings with a maximum length of 65,535 bytes. PostgreSQL also offers a TEXT data type, but it can store strings up to 1GB in size. This difference in capacity may need to be considered when migrating databases.

MEDIUMTEXT in MySQL

The MEDIUMTEXT data type in MySQL is another variable-length string type. It can hold a string with a maximum length of 16,777,215 bytes (or 16MB). Like TINYTEXT, there's no direct equivalent of MEDIUMTEXT in PostgreSQL, but the general TEXT type can handle similar data due to its large capacity.

LONGTEXT in MySQL

LONGTEXT is a MySQL data type used for storing large amounts of text, with a maximum length of 4,294,967,295 bytes (or 4GB). PostgreSQL doesn't have a direct equivalent to the LONGTEXT type, but PostgreSQL's TEXT type should be sufficient for most applications due to its large capacity of 1GB.