PostgreSQL is often considered to provide a wider range of data types compared to
MySQL. This is one of the reasons why PostgreSQL is preferred for certain
applications that require complex data structures or advanced data types. Here’s a
comparison of some key data types supported by both databases:
Numeric Data Types
PostgreSQL:
Smallint, Integer, Bigint
Serial, Bigserial
Numeric, Decimal
Real, Double Precision
Money
MySQL:
Tinyint, Smallint, Mediumint, Int, Bigint
Decimal, Numeric
Float, Double
Bit
Character Data Types
PostgreSQL:
Char, Varchar
Text
Varchar (with length), Char (with length)
Character (with length), Character Varying (with length)
MySQL:
Char, Varchar
Text (Tinytext, Text, Mediumtext, Longtext)
Enum, Set
Date/Time Data Types
PostgreSQL:
Date, Time (without time zone), Time (with time zone)
Timestamp (without time zone), Timestamp (with time zone)
Interval
MySQL:
Date, Time
Datetime, Timestamp
Year
Boolean Data Type
PostgreSQL:
Boolean
MySQL:
Tinyint (used as Boolean)
JSON Data Types
PostgreSQL:
JSON, JSONB
MySQL:
JSON
Geometric Data Types
PostgreSQL:
Point, Line, Lseg, Box, Path, Polygon, Circle
MySQL:
Geometry, Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon,
GeometryCollection
Network Address Types (PostgreSQL specific)
PostgreSQL:
CIDR, INET, MACADDR
UUID
PostgreSQL:
UUID
MySQL:
UUID (stored as CHAR or BINARY, not native)
Array Data Types (PostgreSQL specific)
PostgreSQL:
Arrays of any data type
Range Types (PostgreSQL specific)
PostgreSQL:
Int4range, Int8range, Numrange, Tsrange, Tstzrange, Daterange
Hstore (PostgreSQL specific)
PostgreSQL:
Hstore (key-value pairs)
XML Data Types
PostgreSQL:
XML
MySQL:
XML
