MySQL vs PostgreSQL: Numeric Datatype Comparison

When you think of how to store data in a database, you think of data types. A data type represents a method of how some bit of data is stored internally. Whether it’s numbers, text, dates, or even more complex structures like JSON or geometric shapes, there is an internal format and a method to use that format when you use the data you stored. Along with columns, they are like the base building blocks for your database.

MySQL and PostgreSQL offer numerous ways to store the type of data you collect. So, why are we comparing data types between MySQL and PostgreSQL? The objective is simple: to help you make an informed decision about which DBMS is best suited for your needs.

In this series, we will be comparing the numeric data type along with its strengths and weaknesses and explore how they can be stored in databases. So, let’s get right to it!

Overview of the Numeric Data Types

Numeric data types serve as a foundation in DBMS. It helps represent numbers in different forms which helps in storing and manipulating arithmetic operations on numerical data.

When dealing with numeric data types in databases like MySQL and PostgreSQL, it’s important to understand key concepts like range, precision(fixed-point numbers, floating-point numbers), and storage. This helps you understand how data is stored, processed, and retrieved. Now, let’s breakdown these terms into a more detailed explanation:

Range

The range of a numeric data type is usually the number of bits used to store the value. It defines the minimum and maximum values of bits that can be stored in that data type. Having knowledge about the range of a data type is important as this impacts the choice of data type to be used based on the application’s requirements.

For example, MySQL’s TINYINT has a signed range between -128 to 127. This means that if you use a TINYINT column in MySQL, you can store any whole number that is at least -128 and at most 127. If you try to store a number outside this range, like 200 or -150, MySQL will not be able to store it in a TINYINT column, causing an error, because it falls outside the allowed range.

Precision

Precision is about how many digits a number has in total, including both the whole number part and the decimal part. This is where the usefulness of fixed-point and floating-point numbers comes in.

Fixed-point numbers are a way to represent numbers where you decide in advance how many digits will be on each side of the decimal point. This is useful when you need exact values, like in money calculations, where even a small rounding error can be a problem.

Floating-point numbers are a way to represent real numbers, especially when the numbers are very large or very small. They work kind of like scientific notation, where you have a number (the mantissa) multiplied by a power of ten (the exponent). This allows floating-point numbers to cover a huge range of values, but they might not be as exact as fixed-point numbers. They’re useful when exact precision isn’t as important as being able to handle very big or tiny numbers, like in scientific calculations. For example, MySQL uses a 7 decimal digit precision for float. If you store a number with more than 7 digits, MySQL will round the number to fit within the 7-digit precision. Here’s an example:

CREATE TABLE example_on_precision (

value FLOAT

);

-- Insert a number with more than 7 digits

INSERT INTO example_on_precision (value)

VALUES (12345678.901234);

MySQL rounds the value 12345678.901234 to 12345680, retaining only 7 digits of precision.

Storage

Storage in a database is the amount of space a number takes up when it’s saved. Think of it like saving files on your computer—some files are small, like text documents, and others are large, like videos.

Similarly, in a database, different types of numbers take up different amounts of space. Some numbers are stored in a way that’s very precise , which might take more space, while others are stored in a more approximate way, which might take less space.

MySQL Numeric Data Types

MySQL supports a range of numeric data types including integer types for whole numbers, floating-point types for approximate values, fixed-point types for exact decimal numbers, and bit-value types for compact storage of binary data. Let’s check out some of these numeric data types:

Data Type Name	Description	Common Use cases
`TINYINT`	It is a numeric data type that is ideal for storing very small numbers.	Status flags, age, Boolean values
`SMALLINT`	It is a 2 byte integer type that is used for slightly larger numbers.	Inventory quantities or smaller counters
`MEDIUMINT`	It is a 3 byte integer type that has more range than `SMALLINT`	Larger counters, more precise IDs
`INT`	It is a 4 byte standard integer for general purpose numbers	Age, year, sequence numbers.
`BIGINT`	It is an 8 byte integer for very large numbers.	Unix timestamp
`FLOAT`	It is a single precision floating point number.	Scientific calculations, approximate values
`DOUBLE`	It is a double precision floating point number.	High-precision scientific and engineering calculations
`DECIMAL`	It is an exact fixed-point number with specified precision	Monetary values, fractional quantities.
`BIT`	It stores a sequence of bits (binary digits)	Bitwise operations, flags.

For tiny numbers like ages or small counters, TINYINT and SMALLINT are great because they save space. If you need to store really big numbers, BIGINT is the way to go. When you need exact numbers, like for money, DECIMAL is perfect.

On the other hand, FLOAT and DOUBLE are good for handling very large, very small numbers, or when you need to store a very large range of values but might not be as precise. BIT is useful for storing simple true/false or binary values in a compact way.

Choose data types not just based on current needs but also considering possible future requirements. While using smaller data types saves space, make sure it doesn’t negatively impact performance or future flexibility. For example, using TINYINT for ages is efficient, but if there’s a chance you might store ages beyond 127, opting for SMALLINT could prevent future issues. Now, let’s check out PostgreSQL!

PostgreSQL Numeric Data Types

Just like MySQL, PostgreSQL also has varieties of numeric data types available. From small integers to large, PostgreSQL offers integer types for whole numbers, floating-point types for approximate values, fixed-point types for precise decimals, and serial types for auto-incrementing identifiers. Here are the numeric data types available in PostgreSQL:

Data Type Name	Description	Common Use cases
`SMALLINT`	It is a small integer with a limited range.	Inventory counts, small-scale IDs
`INTEGER`	It is a general-purpose standard integer.	Counters, and any numbers that do not require any decimal precision.
`BIGINT`	It is an integer for very large numbers.	High precision counters, large scale transactions.
`REAL`	It is a single precision floating point number.	Scientific/Approximate measurements.
`DOUBLE PRECISION`	It is a double precision floating point number	High precision scientific calculations
`DECIMAL`/`NUMERIC`	It is an exact fixed-point number with defined precision.	Financial data
`SERIAL`	It is an auto-incrementing integer for unique IDs.	Primary keys
`SMALLSERIAL`	It is an auto-incrementing small integer for unique IDs.	A table with a small number of rows.
BIGSERIAL	It is an auto-incrementing large integer for unique IDs.	A large table with a large number of rows.

For small numbers, like small IDs or counters, SMALLINT is a good choice. If you need to handle regular numbers, INTEGER works well, while BIGINT is perfect for really large numbers, like big financial transactions.

For numbers that don’t need to be exact, like scientific measurements, REAL and DOUBLE PRECISION are used, with DOUBLE PRECISION being more precise. When you need exact numbers, especially for money, NUMERIC and DECIMAL ensure accuracy.

Finally, if you need automatic numbering for rows, PostgreSQL offers SERIAL, BIGSERIAL, and SMALLSERIAL, similar to MySQL’s AUTO_INCREMENT, but with more control over how big the numbers can get. You can also use identity columns using the GENERATED ALWAYS AS IDENTITY for the same purpose with one of the integer datatypes as supported by sequences.

MySQL vs PostgreSQL: Comparison of Numeric Data Types

When you’re deciding how to store numbers in a database, it’s helpful to know how different systems, like MySQL and PostgreSQL, manage them. Both databases have several ways to store numbers, but they handle things like accuracy (precision), the size of the numbers (range), and speed (performance) a bit differently.

MySQL supports unsigned integers for most integer types, which allows you to store only non-negative numbers and effectively doubles the upper range. PostgreSQL does not support unsigned integers, so all integer types in PostgreSQL are signed, meaning they can store both negative and positive numbers.

PostgreSQL has a data type for auto-incrementing fields called the ‘SERIAL’ data type while MySQL uses the AUTO INCREMENT attribute to do this.

Here’s a detailed comparison on numerical data types in MySQL and PostgreSQL:

Data Types	MySQL	PostgreSQL	Range (MySQL)	Range (PostgreSQL)	Precision	Storage
Tiny Integer	‘`TINYINT’`	Not applicable. ‘`SMALLINT’` is the closest alternative.	Signed range: -128 to 127 Unsigned range: 0 to 255	Not applicable	Exact	1 byte.
Small Integer	‘`SMALLINT’`	‘`SMALLINT’`	Signed range: -2¹⁵to 2¹⁵-1 Unsigned range: 0 to 2¹⁶-1	-2¹⁵to 2¹⁵-1	Exact	Identical (2 bytes)
Medium Integer	‘`MEDIUMINT’`	Not applicable.	Signed range: -2²³to 2²³-1 Unsigned range: 0 to 2²⁴-1	–	Exact	3 bytes
Standard Integer	‘`INT’`	‘`INTEGER’`	Signed range: -2³¹to 2³¹-1 Unsigned range: 0 to 2³²-1	-2³¹to 2³¹-1	Exact	Same in both (4 bytes)
Large Integer	‘`BIGINT’`	‘`BIGINT’`	Signed range: -2⁶³to 2⁶³-1 Unsigned range: 0 to 2⁶⁴-1	-2⁶³to 2⁶³-1	Exact	Identical (8 bytes)
Floating point	‘`FLOAT’`	‘`REAL’`	Approx. ±3.402823466E+38, 0, to ±1.175494351E-38	Decimal precision of 6 digits	Approximate	Identical (4 bytes)
Double precision	‘`DOUBLE’`	‘`DOUBLE PRECISION`’	Approx. ±2.2250738585072014E-308, 0, to ±1.7976931348623157E+308 (15 digits decimal precision)	Decimal precision of 15 digits	Approximate	Same in both (8 bytes)
Fixed point	‘`DECIMAL’` ‘`NUMERIC’`	‘`DECIMAL’` ‘`NUMERIC’`	Max. digits: 65 Max. decimal: 30	Max. left digit: 2¹⁷ Max. right digit: 2¹⁴-1	Exact	Identical (Storage size is dependent on no of digits)
Serial	Not applicable	‘`SERIAL’`	Not applicable	1 to 2³¹-1	Exact	4 bytes
Small Serial	Not applicable	‘`SMALLSERIAL’`	Not applicable	1 to 2¹⁵-1	Exact	2 bytes
Big Serial	Not applicable	‘`BIGSERIAL’`	Not applicable	1 to 2⁶³-1	Exact	8 bytes
Bit	BIT	`BIT`(although a string type in PG)	1 to 64	User-specified	Exact	1 byte

Let’s take a look at some examples and compare them:

Unsigned Integers and TINYINT

One of the significant differences between MySQL and PostgreSQL is how they handle unsigned integers. In MySQL, you can declare an integer as UNSIGNED, which allows you to store only non-negative numbers, effectively doubling the upper range. Let’s see how this works:

CREATE TABLE employee_salaries (

employee_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

salary TINYINT UNSIGNED -- size for example only

);

-- Insert valid salary values

INSERT INTO employee_salaries (salary)

VALUES (120);

INSERT INTO employee_salaries (salary)

VALUES (200);

-- Attempting to insert a negative value will result in an error

INSERT INTO employee_salaries (salary)

VALUES (-50); -- This will cause an error

select *

from employee_salaries;

The error when inserting the negative value into the UNSIGNED TINYINT is:
SQL Error [1264] [22001]: Data truncation: Out of range value for column 'salary' at row 1

Also, a TINYINT in MySQL can store values from 0 to 255 when unsigned. Attempting to store a value greater than 255 using the TINYINT data type will result in an error. Here’s an example:

-- Attempting to insert a value greater than 255 will

-- result in an error

-- This will cause an error

INSERT INTO employee_salaries (salary)

VALUES (350);

The error will be the exact same as for the negative value we previously tried to insert, which can make debugging kind of interesting.

In PostgreSQL, however, there’s no concept of UNSIGNED integers or a TINYINT data type (SMALLINT is the closest alternative). All integer types in PostgreSQL are signed, meaning they can store both positive and negative numbers. Let’s take a look:

CREATE TABLE employee_salaries (

employee_id SERIAL PRIMARY KEY,

salary INT<br>);

-- Insert valid salary values

INSERT INTO employee_salaries (salary)

VALUES (120);

INSERT INTO employee_salaries (salary)

VALUES (200);

-- In PostgreSQL, you can insert negative values, which might not be desirable

INSERT INTO employee_salaries (salary)

VALUES (-50); -- This will be accepted

select * from employee_salaries;

The difference with Unsigned Integers and the TINYINT data type in both databases is crucial if you need to store only positive values and when you need to maximize the storage range.

Note that the common way to handle this when you need an unsigned value but do not want negative values is to use a CHECK constraint. CHECK constraints should also be used when you need less values than a datatype can hold.

So for a salary, you might define the following CHECK constraint, in which case you would receive an error about violating the CHECK constraint.

ALTER TABLE employee_salaries

ADD CONSTRAINT salary_range

CHECK (salary BETWEEN 0 and 1000000)

Floating Point (FLOAT vs. REAL) and Double Precision (DOUBLE vs. DOUBLE PRECISION)

The primary difference between these types lies in their naming conventions and slight variations in precision. In MySQL, the terms FLOAT and DOUBLE are used, while PostgreSQL uses REAL and DOUBLE PRECISION.

MySQL’s FLOAT offers around 7 digits of precision, whereas PostgreSQL’s REAL provides slightly less, with approximately 6 digits of precision. For double-precision types, both MySQL’s DOUBLE and PostgreSQL’s DOUBLE PRECISION offer the same level of precision, up to 15 digits.

Despite the differences in names and minor variations in precision, these types are functionally quite similar between the two databases. Let’s fire up both databases to see them in action!

MySQL

CREATE TABLE measurements (

building_height FLOAT, -- Less precision

exact_measurement DOUBLE -- More precision

);

-- Inserting values with many decimal places to observe rounding in FLOAT

INSERT INTO measurements (building_height, exact_measurement)

VALUES (123.456789012345, 123.4567890123456789);

INSERT INTO measurements (building_height, exact_measurement)

VALUES (567.890123456789, 567.8901234567890123);

SELECT building_height as building_height_float,

exact_measurement as exact_measurement_double

FROM measurements;

This returns:

|building_height|exact_measurement|

|---------------|-----------------|

|123.457 |123.4567890123 |

|567.89 |567.8901234568 |

PostgreSQL

CREATE TABLE measurements

(

building_height REAL, -- Less precision

exact_measurement DOUBLE PRECISION -- More precision

);

-- Inserting values with many decimal places to observe

-- rounding in FLOAT

INSERT INTO measurements (building_height, exact_measurement)

VALUES (123.456789012345, 123.4567890123456789);

INSERT INTO measurements (building_height, exact_measurement)

VALUES (567.890123456789, 567.8901234567890123);

SELECT building_height as building_height_float,

exact_measurement as exact_measurement_double_precision

FROM measurements;

This returns:

building_height_float|exact_measurement_double_precision|

---------------------+----------------------------------+

123.45679| 123.45678901234568|

567.89014| 567.890123456789|

Both databases handle floating-point precision similarly, with FLOAT/REAL providing lower precision and DOUBLE/DOUBLE PRECISION offering higher precision. Also, MySQL and PostgreSQL use the same amount of storage for these types- 4 bytes for FLOAT/REAL and 8 bytes for DOUBLE/DOUBLE PRECISION

BIT(MySQL) vs BIT(PostgreSQL)

When comparing the BIT data type in MySQL and PostgreSQL, there are a few key differences that are important to understand, especially if you’re new to databases. The BIT data type in MySQL is used to store binary values, which are sequences of bits (like 1s and 0s). You can specify the number of bits you want to store, up to 64 bits. For example, BIT(8) can store up to 8 bits (e.g., 10101010). If no length is specified, it defaults to 1 bit. Here’s an example:

CREATE TABLE my_table (

status BIT

);

INSERT INTO my_table (status)

VALUES (b'1');

select *

from my_table;

This returns a value of 1.

If you try to insert a value other than 0 or 1, you will receive an error.

1 2	INSERT INTO my_table (status) VALUES (b'2');

The error isn’t super clear:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b'2')' at line 1

In PostgreSQL, the BIT type is also used to store bit strings. Similar to MySQL, you can specify the length, but PostgreSQL separates BIT into two types: BIT(n) for a fixed-length bit string and BIT VARYING(n) for a variable-length bit string. Like MySQL, if no length is specified, BIT stores a single bit. Let’s see how it works in PostgreSQL:

CREATE TABLE my_table (

status BIT(8)

);

INSERT INTO my_table (status)

VALUES (B'10101010');

select *<br>from my_table

This returns:

status

--------

10101010

If you try to insert a non-binary value, you will get an error like this:

1 2	INSERT INTO my_table (status) VALUES (B'G0101010');

This will return an error:

SQL Error [22P02]: ERROR: "G" is not a valid binary digit

Differences to look out for? PostgreSQL allows for variable-length bit strings using BIT VARYING, whereas MySQL only supports fixed-length bit fields. Inserting values into BIT columns is also slightly different. In MySQL, you use b'...', while in PostgreSQL, you use B'...' for binary literals.

It’s also important to note that PostgreSQL treats BIT more like a string of bits, while MySQL stores the value as a binary representation.

Serial Types: AUTO_INCREMENT in MySQL vs. SERIAL in PostgreSQL

When working with primary keys or unique identifiers in databases, it’s common to use auto-incrementing fields to generate unique IDs automatically. Both MySQL and PostgreSQL offer mechanisms for this, but they differ in their implementations: MySQL uses AUTO_INCREMENT, while PostgreSQL uses SERIAL. Let’s look at how this works in both databases:

MYSQL

CREATE TABLE orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,

product_name VARCHAR (255) NOT NULL );

INSERT INTO orders (product_name)

VALUES ('Laptop');

INSERT INTO orders (product_name)

VALUES ('Smartphone');

select * from orders;

The output of this if you execute it as is will be:

order_id product_name

----------- --------------------

1 Laptop

2 Smartphone

Every row will have an incremented value for order_id as you insert new rows..

PostgreSQL

CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,

product_name VARCHAR(255)

);

INSERT INTO orders (product_name)

VALUES ('Laptop');

INSERT INTO orders (product_name)

VALUES ('Smartphone');

select * from orders;

The output of this code will be exactly like the MySQL output.

In MySQL, AUTO_INCREMENT is an attribute that you apply to an integer column, whereas in PostgreSQL, SERIAL is a distinct data type. This means that in PostgreSQL, using SERIAL also creates a sequence object behind the scenes that you can interact with if needed.

PostgreSQL’s SERIAL offers more control over the sequence. For instance, you can reset the sequence, set it to start at a specific number, or alter its increment value using the SETVAL function.

CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,

product_name VARCHAR(255)

);

-- Reset the sequence to start from 5

SELECT SETVAL(pg_get_serial_sequence('orders', 'order_id'), 4, true);

INSERT INTO orders (product_name) VALUES ('Laptop');

INSERT INTO orders (product_name) VALUES ('Smartphone');

select * from orders;

The output of this code is:

order_id product_name

-------- ------------

5 Laptop

6 Smartphone

In PostgreSQL, the SETVAL function is used to set the current value of a sequence, hence the value starts back at 5. The function typically takes three arguments: the sequence name, the new value for the sequence, and a Boolean value (is_called) that determines whether the next sequence value should be returned as-is or incremented.

1	SETVAL(sequence_name, value, is_called)

The sequence_name is the name of the sequence you want to reset. You can get this name using the pg_get_serial_sequence function, which retrieves the sequence associated with a specific column in a table. In this case, it retrieves the name of the sequence associated with the order_id column in the orders table.

The value is the number you want the sequence to start from, usually an integer of your choice. For example, if you set it to 4, the next time you insert a new row, the sequence will start from 5.

The is_called is a Boolean value. If set to true, the sequence will immediately move to the next value, which in this case would be 5. If false, the sequence would stay at 4 until the next value is needed. By omitting the third argument (is_called), PostgreSQL assumes the default value, which is true.

Summary

MySQL and PostgreSQL offer a variety of ways to store numbers, and while they share many similarities, there are key differences that were highlighted in this article. We broke down the basics so you can confidently pick the right numerical data types for your database.

If you need simplicity, and the ability to work with unsigned integers- choose MySQL. If you prioritize precision, advanced features, and flexibility, especially in cases where exact numerical representation is critical, then go for PostgreSQL. Happy querying!

Register for Simple Talk

MySQL vs PostgreSQL: Numeric Datatype Comparison

Overview of the Numeric Data Types

Range

Precision

Storage

MySQL Numeric Data Types

PostgreSQL Numeric Data Types

MySQL vs PostgreSQL: Comparison of Numeric Data Types

Unsigned Integers and TINYINT

Floating Point (FLOAT vs. REAL) and Double Precision (DOUBLE vs. DOUBLE PRECISION)

BIT(MySQL) vs BIT(PostgreSQL)

Serial Types: AUTO_INCREMENT in MySQL vs. SERIAL in PostgreSQL

Summary

About the author

Aisha Bukar

Aisha's contributions

Articles

Books

Top topics

Aisha's latest contributions:

MySQL vs PostgreSQL: Which Open-Source Database is right for you?

A Beginners Guide To MySQL Replication Part 6: Security Considerations in MySQL Replication

A Beginners Guide to MySQL Replication Part 5: Group Replication

Overview of the Numeric Data Types

Range

Precision

Storage

MySQL Numeric Data Types

PostgreSQL Numeric Data Types

MySQL vs PostgreSQL: Comparison of Numeric Data Types

Unsigned Integers and TINYINT

Floating Point (FLOAT vs. REAL) and Double Precision (DOUBLE vs. DOUBLE PRECISION)

BIT(MySQL) vs BIT(PostgreSQL)

Serial Types: AUTO_INCREMENT in MySQL vs. SERIAL in PostgreSQL

Summary

Recommended

About the author

Aisha Bukar

Aisha's contributions

Articles

Books

Top topics

Aisha's latest contributions:

MySQL vs PostgreSQL: Which Open-Source Database is right for you?

A Beginners Guide To MySQL Replication Part 6: Security Considerations in MySQL Replication

A Beginners Guide to MySQL Replication Part 5: Group Replication