{"id":104261,"date":"2024-11-15T21:09:49","date_gmt":"2024-11-15T21:09:49","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104261"},"modified":"2025-02-07T22:52:06","modified_gmt":"2025-02-07T22:52:06","slug":"mysql-vs-postgresql-numeric-datatype-comparison","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql-vs-postgresql-numeric-datatype-comparison\/","title":{"rendered":"MySQL vs PostgreSQL:\u00a0Numeric Datatype Comparison"},"content":{"rendered":"\n

This is part of a series of posts from Aisha Bukar comparing MySQL and PostgreSQL. You can see the entire series here<\/a>.<\/strong><\/p>\n\n\n\n\n

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.<\/p>\n\n\n\n

 <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

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\u2019s get right to it!<\/p>\n\n\n\n

<\/a>Overview of the Numeric Data Types<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

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\u2019s breakdown these terms into a more detailed explanation:<\/p>\n\n\n\n

<\/a>Range<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

For example, MySQL’s TINYINT<\/code> has a signed range between -128 to 127. This means that if you use a TINYINT<\/code> 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<\/code> column, causing an error, because it falls outside the allowed range.<\/p>\n\n\n\n

Precision<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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\u2019s an example:<\/p>\n\n\n\n

CREATE TABLE example_on_precision (\n  value FLOAT\n);\n\n-- Insert a number with more than 7 digits\nINSERT INTO example_on_precision (value) \nVALUES (12345678.901234);<\/pre>\n\n\n\n
MySQL rounds the value 12345678.901234<\/code> to 12345680<\/code>, retaining only 7 digits of precision.<\/p>\n\n\n\n
<\/a>Storage<\/h3>\n\n\n\n
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\u2014some files are small, like text documents, and others are large, like videos.<\/p>\n\n\n\n
Similarly, in a database, different types of numbers take up different amounts of space. Some numbers are stored in a way that\u2019s very precise , which might take more space, while others are stored in a more approximate way, which might take less space.<\/p>\n\n\n\n
<\/a>MySQL Numeric Data Types<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
\n
 Data Type Name<\/strong><\/p>\n<\/td>
\n
 Description<\/strong><\/p>\n<\/td>
\n
 Common Use cases<\/strong><\/p>\n<\/td><\/tr>
\n
TINYINT<\/code><\/p>\n<\/td>
\n
It is a numeric data type that is ideal for storing very small numbers.<\/p>\n<\/td>
\n
Status flags, age, Boolean values<\/p>\n<\/td><\/tr>
\n
SMALLINT<\/code><\/p>\n<\/td>
\n
It is a 2 byte integer type that is used for slightly larger numbers.<\/p>\n<\/td>
\n
Inventory quantities or smaller counters<\/p>\n<\/td><\/tr>
\n
MEDIUMINT<\/code><\/p>\n<\/td>
\n
It is a 3 byte integer type that has more range than SMALLINT<\/code><\/p>\n<\/td>
\n
Larger counters, more precise IDs<\/p>\n<\/td><\/tr>
\n
INT<\/code><\/p>\n<\/td>
\n
It is a 4 byte standard integer for general purpose numbers<\/p>\n<\/td>
\n
Age, year, sequence numbers.<\/p>\n<\/td><\/tr>
\n
BIGINT<\/code><\/p>\n<\/td>
\n
It is an 8 byte integer for very large numbers.<\/p>\n<\/td>
\n
Unix timestamp<\/p>\n<\/td><\/tr>
\n
FLOAT<\/code><\/p>\n<\/td>
\n
It is a single precision floating point number.<\/p>\n<\/td>
\n
Scientific calculations, approximate values<\/p>\n<\/td><\/tr>
\n
DOUBLE<\/code><\/p>\n<\/td>
\n
It is a double precision floating point number.<\/p>\n<\/td>
\n
High-precision scientific and engineering calculations<\/p>\n<\/td><\/tr>
\n
DECIMAL<\/code><\/p>\n<\/td>
\n
It is an exact fixed-point number with specified precision<\/p>\n<\/td>
\n
Monetary values, fractional quantities.<\/p>\n<\/td><\/tr>
\n
BIT<\/code><\/p>\n<\/td>
\n
It stores a sequence of bits (binary digits)<\/p>\n<\/td>
\n
Bitwise operations, flags.<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
For tiny numbers like ages or small counters, TINYINT<\/code> and SMALLINT<\/code> are great because they save space. If you need to store really big numbers, BIGINT<\/code> is the way to go. When you need exact numbers, like for money, DECIMAL<\/code> is perfect.<\/p>\n\n\n\n
On the other hand, FLOAT<\/code> and DOUBLE<\/code> 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<\/code> is useful for storing simple true\/false or binary values in a compact way.<\/p>\n\n\n\n
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\u2019t negatively impact performance or future flexibility. For example, using TINYINT<\/code> for ages is efficient, but if there\u2019s a chance you might store ages beyond 127, opting for SMALLINT<\/code> could prevent future issues. Now, let\u2019s check out PostgreSQL!<\/p>\n\n\n\n
<\/a>PostgreSQL Numeric Data Types<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
\n
 Data Type Name<\/strong><\/p>\n<\/td>
\n
 Description<\/strong><\/p>\n<\/td>
\n
 Common Use cases<\/strong><\/p>\n<\/td><\/tr>
\n
SMALLINT<\/code><\/p>\n<\/td>
\n
It is a small integer with a limited range.<\/p>\n<\/td>
\n
Inventory counts, small-scale IDs<\/p>\n<\/td><\/tr>
\n
INTEGER<\/code><\/p>\n<\/td>
\n
It is a general-purpose standard integer.<\/p>\n<\/td>

Counters, and any numbers that do not require any decimal precision.<\/p>
<\/td><\/tr>
\n
BIGINT<\/code><\/p>\n<\/td>
\n
It is an integer for very large numbers.<\/p>\n<\/td>
\n
High precision counters, large scale transactions.<\/p>\n<\/td><\/tr>
\n
REAL<\/code><\/p>\n<\/td>
\n
It is a single precision floating point number.<\/p>\n<\/td>
\n
Scientific\/Approximate measurements.<\/p>\n<\/td><\/tr>
\n
DOUBLE PRECISION<\/code><\/p>\n<\/td>
\n
It is a double precision floating point number<\/p>\n<\/td>
\n
High precision scientific calculations<\/p>\n<\/td><\/tr>
\n
DECIMAL<\/code>\/NUMERIC<\/code><\/p>\n<\/td>
\n
It is an exact fixed-point number with defined precision.<\/p>\n<\/td>
\n
Financial data<\/p>\n<\/td><\/tr>
\n
SERIAL<\/code><\/p>\n<\/td>
\n
It is an auto-incrementing integer for unique IDs.<\/p>\n<\/td>
\n
Primary keys<\/p>\n<\/td><\/tr>
\n
SMALLSERIAL<\/code><\/p>\n<\/td>
\n
It is an auto-incrementing small integer for unique IDs.<\/p>\n<\/td>
\n
A table with a small number of rows.<\/p>\n<\/td><\/tr>
\n
BIGSERIAL<\/p>\n<\/td>
\n
It is an auto-incrementing large integer for unique IDs.<\/p>\n<\/td>
\n
A large table with a large number of rows.<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
For small numbers, like small IDs or counters, SMALLINT<\/code> is a good choice. If you need to handle regular numbers, INTEGER<\/code> works well, while BIGINT<\/code> is perfect for really large numbers, like big financial transactions.<\/p>\n\n\n\n
For numbers that don’t need to be exact, like scientific measurements, REAL<\/code> and DOUBLE PRECISION <\/code>are used, with DOUBLE PRECISION <\/code>being more precise. When you need exact numbers, especially for money, NUMERIC<\/code> and DECIMAL<\/code> ensure accuracy.<\/p>\n\n\n\n
Finally, if you need automatic numbering for rows, PostgreSQL offers SERIAL<\/code>, BIGSERIAL<\/code>, and SMALLSERIAL<\/code>, similar to MySQL’s AUTO_INCREMENT<\/code>, but with more control over how big the numbers can get. You can also use identity columns using the GENERATED ALWAYS AS IDENTITY<\/code> for the same purpose<\/a> with one of the integer datatypes as supported by sequences<\/a>.<\/p>\n\n\n\n
<\/a>MySQL vs PostgreSQL: Comparison of Numeric Data Types<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
PostgreSQL has a data type for auto-incrementing fields called the \u2018SERIAL\u2019<\/code> data type while MySQL uses the AUTO INCREMENT<\/code> attribute to do this.<\/p>\n\n\n\n
Here’s a detailed comparison on numerical data types in MySQL and PostgreSQL:<\/p>\n\n\n\n
\n
Data Types<\/strong><\/p>\n<\/td>
\n
MySQL<\/strong><\/p>\n<\/td>
\n
PostgreSQL<\/strong><\/p>\n<\/td>
\n
Range (MySQL)<\/strong><\/p>\n<\/td>
\n
Range (PostgreSQL)<\/strong><\/p>\n<\/td>
\n
Precision<\/strong><\/p>\n<\/td>
\n
Storage<\/strong><\/p>\n<\/td><\/tr>
\n
Tiny Integer<\/p>\n<\/td>
\n
\u2018TINYINT\u2019<\/code><\/p>\n<\/td>
\n
Not applicable.<\/p>\n
\u2018SMALLINT\u2019<\/code> is the closest alternative.<\/p>\n<\/td>
\n
Signed range: -128 to 127<\/p>\n
Unsigned range: 0 to 255<\/p>\n<\/td>
\n
Not applicable<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
1 byte.<\/p>\n<\/td><\/tr>
\n
Small Integer<\/p>\n<\/td>
\n
\u2018SMALLINT\u2019<\/code><\/p>\n<\/td>
\n
\u2018SMALLINT\u2019<\/code><\/p>\n<\/td>
\n
Signed range: -215 <\/sup>to 215<\/sup>-1<\/p>\n
Unsigned range: 0 to 216<\/sup>-1<\/p>\n<\/td>
\n
-215 <\/sup>to 215<\/sup>-1<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
Identical (2 bytes)<\/p>\n<\/td><\/tr>
\n
Medium Integer<\/p>\n<\/td>
\n
\u2018MEDIUMINT\u2019<\/code><\/p>\n<\/td>
\n
Not applicable.<\/p>\n<\/td>
\n
Signed range: -223 <\/sup>to 223<\/sup>-1<\/p>\n
Unsigned range: 0 to 224<\/sup>-1 <\/p>\n<\/td>
\n
         –<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
3 bytes<\/p>\n<\/td><\/tr>
\n
Standard Integer<\/p>\n<\/td>
\n
\u2018INT\u2019<\/code><\/p>\n<\/td>
\n
\u2018INTEGER\u2019<\/code><\/p>\n<\/td>
\n
Signed range: -231 <\/sup>to 231<\/sup>-1<\/p>\n
Unsigned range: 0 to 232<\/sup>-1<\/p>\n<\/td>
\n
-231 <\/sup>to 231<\/sup>-1<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
Same in both (4 bytes)<\/p>\n<\/td><\/tr>
\n
Large Integer<\/p>\n<\/td>
\n
\u2018BIGINT\u2019<\/code><\/p>\n<\/td>
\n
\u2018BIGINT\u2019<\/code><\/p>\n<\/td>
\n
Signed range: -263 <\/sup>to 263<\/sup>-1<\/p>\n
Unsigned range: 0 to 264 <\/sup>-1<\/p>\n<\/td>
\n
-263 <\/sup>to 263<\/sup>-1<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
Identical (8 bytes)<\/p>\n<\/td><\/tr>
\n
Floating point<\/p>\n<\/td>
\n
\u2018FLOAT\u2019<\/code><\/p>\n<\/td>
\n
\u2018REAL\u2019<\/code><\/p>\n<\/td>
\n
Approx.<\/p>\n
\u00b13.402823466E+38, 0, to \u00b11.175494351E-38<\/p>\n<\/td>
\n
Decimal precision of 6 digits <\/p>\n<\/td>
\n
Approximate<\/p>\n<\/td>
\n
Identical (4 bytes)<\/p>\n<\/td><\/tr>
\n
Double precision<\/p>\n<\/td>
\n
\u2018DOUBLE\u2019<\/code><\/p>\n<\/td>
\n
\u2018DOUBLE PRECISION<\/code>\u2019<\/p>\n<\/td>
\n
Approx. \u00b12.2250738585072014E-308, 0, to \u00b11.7976931348623157E+308 (15 digits decimal precision)<\/p>\n<\/td>
\n
Decimal precision of 15 digits<\/p>\n<\/td>
\n
Approximate<\/p>\n<\/td>
\n
Same in both (8 bytes)<\/p>\n<\/td><\/tr>
\n
Fixed point<\/p>\n<\/td>
\n
\u2018DECIMAL\u2019<\/code><\/p>\n
\u2018NUMERIC\u2019<\/code><\/p>\n<\/td>
\n
\u2018DECIMAL\u2019<\/code><\/p>\n
\u2018NUMERIC\u2019<\/code><\/p>\n<\/td>
\n
Max. digits: 65<\/p>\n
Max. decimal: 30<\/p>\n<\/td>
\n
Max. left digit: 217<\/sup><\/p>\n
Max. right digit: 214<\/sup>-1<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
Identical (Storage size is dependent on no of digits)<\/p>\n<\/td><\/tr>
\n
Serial<\/p>\n<\/td>
\n
Not applicable <\/p>\n<\/td>
\n
\u2018SERIAL\u2019<\/code><\/p>\n<\/td>
\n
  Not applicable<\/p>\n<\/td>
\n
1 to 231<\/sup>-1<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
4 bytes <\/p>\n<\/td><\/tr>
\n
Small Serial<\/p>\n<\/td>
\n
Not applicable<\/p>\n<\/td>
\n
\u2018SMALLSERIAL\u2019<\/code><\/p>\n<\/td>
\n
Not applicable<\/p>\n<\/td>
\n
1 to  215<\/sup>-1<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
2 bytes<\/p>\n<\/td><\/tr>
\n
Big Serial<\/p>\n<\/td>
\n
Not applicable<\/p>\n<\/td>
\n
\u2018BIGSERIAL\u2019<\/code><\/p>\n<\/td>
\n
Not applicable<\/p>\n<\/td>
\n
1 to  263<\/sup>-1<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
8 bytes<\/p>\n<\/td><\/tr>
\n
Bit<\/p>\n<\/td>
\n
BIT<\/p>\n<\/td>
\n
BIT<\/code>(although a string type in PG)<\/p>\n<\/td>
\n
1 to 64<\/p>\n<\/td>
\n
User-specified<\/p>\n<\/td>
\n
Exact<\/p>\n<\/td>
\n
1 byte<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Let\u2019s take a look at some examples and compare them:<\/p>\n\n\n\n
<\/a>Unsigned Integers and TINYINT<\/h3>\n\n\n\n
One of the significant differences between MySQL and PostgreSQL is how they handle unsigned integers. In MySQL, you can declare an integer as UNSIGNED<\/code>, which allows you to store only non-negative numbers, effectively doubling the upper range. Let\u2019s see how this works:<\/p>\n\n\n\n
CREATE TABLE employee_salaries (\n    employee_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n    salary TINYINT UNSIGNED -- size for example only\n);\n\n-- Insert valid salary values\nINSERT INTO employee_salaries (salary) \nVALUES (120);\n\nINSERT INTO employee_salaries (salary) \nVALUES (200);\n\n-- Attempting to insert a negative value will result in an error\n\nINSERT INTO employee_salaries (salary) \nVALUES (-50); -- This will cause an error\n\nselect * \nfrom employee_salaries;<\/pre>\n\n\n\n
The error when inserting the negative value into the UNSIGNED TINYINT <\/code>is:
SQL Error [1264] [22001]: Data truncation: Out of range value for column 'salary' at row 1<\/code><\/p>\n\n\n\n
Also, a TINYINT<\/code> in MySQL can store values from 0 to 255 when unsigned. Attempting to store a value greater than 255 using the TINYINT<\/code> data type will result in an error. Here\u2019s an example:<\/p>\n\n\n\n
-- Attempting to insert a value greater than 255 will \n-- result in an error\n\n-- This will cause an error\nINSERT INTO employee_salaries (salary) \nVALUES (350); <\/pre>\n\n\n\n
The error will be the exact same as for the negative value we previously tried to insert, which can make debugging kind of interesting.<\/p>\n\n\n\n
In PostgreSQL, however, there\u2019s no concept of UNSIGNED<\/code> integers or a TINYINT<\/code> data type (SMALLINT<\/code> is the closest alternative). All integer types in PostgreSQL are signed, meaning they can store both positive and negative numbers. Let\u2019s take a look:<\/p>\n\n\n\n
CREATE TABLE employee_salaries (\n    employee_id SERIAL PRIMARY KEY,\n    salary INT
);\n\n-- Insert valid salary values\nINSERT INTO employee_salaries (salary) \nVALUES (120);\nINSERT INTO employee_salaries (salary) \nVALUES (200);\n\n-- In PostgreSQL, you can insert negative values, which might not be desirable\nINSERT INTO employee_salaries (salary) \nVALUES (-50); -- This will be accepted\n\nselect * from employee_salaries;<\/pre>\n\n\n\n
The difference with Unsigned Integers and the TINYINT<\/code> data type in both databases is crucial if you need to store only positive values and when you need to maximize the storage range.<\/p>\n\n\n\n
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<\/code> constraint. CHECK<\/code> constraints should also be used when you need less values than a datatype can hold.<\/p>\n\n\n\n
So for a salary, you might define the following CHECK<\/code> constraint, in which case you would receive an error about violating the CHECK<\/code> constraint.<\/p>\n\n\n\n
ALTER TABLE employee_salaries \n   ADD CONSTRAINT salary_range \n    CHECK (salary BETWEEN 0 and 1000000)<\/pre>\n\n\n\n
<\/a><\/a>Floating Point (FLOAT vs. REAL) and Double Precision (DOUBLE vs. DOUBLE PRECISION)<\/h3>\n\n\n\n
The primary difference between these types lies in their naming conventions and slight variations in precision. In MySQL, the terms FLOAT<\/code> and DOUBLE<\/code> are used, while PostgreSQL uses REAL<\/code> and DOUBLE PRECISION<\/code>.<\/p>\n\n\n\n
MySQL’s FLOAT<\/code> offers around 7 digits of precision, whereas PostgreSQL’s REAL<\/code> provides slightly less, with approximately 6 digits of precision. For double-precision types, both MySQL’s DOUBLE<\/code> and PostgreSQL’s DOUBLE PRECISION<\/code> offer the same level of precision, up to 15 digits.<\/p>\n\n\n\n
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!<\/p>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
CREATE TABLE measurements (\n   building_height FLOAT, -- Less precision\n   exact_measurement DOUBLE -- More precision\n);\n\n-- Inserting values with many decimal places to observe rounding in FLOAT \nINSERT INTO measurements (building_height, exact_measurement)\nVALUES (123.456789012345, 123.4567890123456789);\n\nINSERT INTO measurements (building_height, exact_measurement)\nVALUES (567.890123456789, 567.8901234567890123);\n\nSELECT building_height as building_height_float,\n       exact_measurement as exact_measurement_double\nFROM measurements;<\/pre>\n\n\n\n
This returns:<\/p>\n\n\n\n
|building_height|exact_measurement|\n|---------------|-----------------|\n|123.457        |123.4567890123   |\n|567.89         |567.8901234568   |<\/pre>\n\n\n\n
PostgreSQL<\/strong><\/p>\n\n\n\n
CREATE TABLE measurements \n(\n    building_height REAL,  -- Less precision\n    exact_measurement DOUBLE PRECISION -- More precision\n);\n-- Inserting values with many decimal places to observe \n-- rounding in FLOAT\nINSERT INTO measurements (building_height, exact_measurement)\nVALUES (123.456789012345, 123.4567890123456789);\n\nINSERT INTO measurements (building_height, exact_measurement)\nVALUES (567.890123456789, 567.8901234567890123);\n\nSELECT building_height as building_height_float,\n       exact_measurement as exact_measurement_double_precision\nFROM measurements;<\/pre>\n\n\n\n
This returns:<\/p>\n\n\n\n
building_height_float|exact_measurement_double_precision|\n---------------------+----------------------------------+\n            123.45679|                123.45678901234568|\n            567.89014|                  567.890123456789|<\/pre>\n\n\n\n
Both databases handle floating-point precision similarly, with FLOAT<\/code>\/REAL<\/code> providing lower precision and DOUBLE<\/code>\/DOUBLE PRECISION<\/code> offering higher precision. Also, MySQL and PostgreSQL use the same amount of storage for these types- 4 bytes for FLOAT<\/code>\/REAL<\/code> and 8 bytes for DOUBLE<\/code>\/DOUBLE PRECISION<\/code><\/p>\n\n\n\n
<\/a>BIT(MySQL) vs BIT(PostgreSQL)<\/h3>\n\n\n\n
When comparing the BIT<\/code> 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<\/code> 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)<\/code> can store up to 8 bits (e.g., 10101010). If no length is specified, it defaults to 1 bit. Here’s an example:<\/p>\n\n\n\n
CREATE TABLE my_table (\n    status BIT\n);\n\nINSERT INTO my_table (status) \nVALUES (b'1');\n\nselect *\nfrom   my_table;<\/pre>\n\n\n\n
This returns a value of 1.<\/p>\n\n\n\n
If you try to insert a value other than 0 or 1, you will receive an error.<\/p>\n\n\n\n
INSERT INTO my_table (status) \nVALUES (b'2');<\/pre>\n\n\n\n
The error isn\u2019t super clear:<\/p>\n\n\n\n
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<\/code><\/p>\n\n\n\n
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\u2019s see how it works in PostgreSQL:<\/p>\n\n\n\n
CREATE TABLE my_table (\n   status BIT(8)\n);\n\nINSERT INTO my_table (status) \nVALUES (B'10101010');\n\nselect *
from my_table<\/pre>\n\n\n\n
This returns:<\/p>\n\n\n\n
status\n-------- \n10101010<\/pre>\n\n\n\n
If you try to insert a non-binary value, you will get an error like this:<\/p>\n\n\n\n
INSERT INTO my_table (status) \nVALUES (B'G0101010');<\/pre>\n\n\n\n
This will return an error:<\/p>\n\n\n\n
SQL Error [22P02]: ERROR: \"G\" is not a valid binary digit<\/code><\/p>\n\n\n\n
Differences to look out for? PostgreSQL allows for variable-length bit strings using BIT VARYING<\/code>, whereas MySQL only supports fixed-length bit fields. Inserting values into BIT columns is also slightly different. In MySQL, you use b'...'<\/code>, while in PostgreSQL, you use B'...'<\/code> for binary literals.<\/p>\n\n\n\n
It\u2019s also important to note that PostgreSQL treats BIT more like a string of bits, while MySQL stores the value as a binary representation.<\/p>\n\n\n\n
<\/a>Serial Types: AUTO_INCREMENT in MySQL vs. SERIAL in PostgreSQL<\/h3>\n\n\n\n
When working with primary keys or unique identifiers in databases, it\u2019s 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<\/code>, while PostgreSQL uses SERIAL<\/code>. Let\u2019s look at how this works in both databases:<\/p>\n\n\n\n
MYSQL<\/strong><\/p>\n\n\n\n
CREATE TABLE orders (\n     order_id INT AUTO_INCREMENT PRIMARY KEY,\n     product_name VARCHAR (255) NOT NULL ); \n\nINSERT INTO orders (product_name) \nVALUES ('Laptop'); \nINSERT INTO orders (product_name) \nVALUES ('Smartphone'); \n\nselect * from orders;<\/pre>\n\n\n\n
The output of this if you execute it as is will be:<\/p>\n\n\n\n
order_id    product_name\n----------- --------------------\n1           Laptop\n2           Smartphone<\/pre>\n\n\n\n
Every row will have an incremented value for order_id as you insert new rows..<\/p>\n\n\n\n
PostgreSQL<\/strong><\/p>\n\n\n\n
CREATE TABLE orders (\n    order_id SERIAL PRIMARY KEY,\n    product_name VARCHAR(255)\n);\n\nINSERT INTO orders (product_name) \nVALUES ('Laptop');\nINSERT INTO orders (product_name) \nVALUES ('Smartphone');\n\nselect * from orders;<\/pre>\n\n\n\n
The output of this code will be exactly like the MySQL output.<\/p>\n\n\n\n
In MySQL, AUTO_INCREM<\/code>ENT is an attribute that you apply to an integer column, whereas in PostgreSQL, SERIAL<\/code> is a distinct data type. This means that in PostgreSQL, using SERIAL<\/code> also creates a sequence object behind the scenes that you can interact with if needed.<\/p>\n\n\n\n
PostgreSQL\u2019s SERIAL<\/code> 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<\/code> function.<\/p>\n\n\n\n
CREATE TABLE orders (\n    order_id SERIAL PRIMARY KEY,\n    product_name VARCHAR(255)\n);\n-- Reset the sequence to start from 5\nSELECT SETVAL(pg_get_serial_sequence('orders', 'order_id'), 4, true);\n\nINSERT INTO orders (product_name) VALUES ('Laptop');\nINSERT INTO orders (product_name) VALUES ('Smartphone');\n\nselect * from orders;<\/pre>\n\n\n\n
The output of this code is:<\/p>\n\n\n\n
order_id product_name \n-------- ------------ \n       5 Laptop       \n       6 Smartphone   <\/pre>\n\n\n\n
In PostgreSQL, the SETVAL<\/code> 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<\/code>) that determines whether the next sequence value should be returned as-is or incremented.<\/p>\n\n\n\n
SETVAL(sequence_name, value, is_called)<\/pre>\n\n\n\n
The sequence_name<\/strong> is the name of the sequence you want to reset. You can get this name using the pg_get_serial_sequence<\/strong> 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<\/strong> column in the orders<\/strong> table.<\/p>\n\n\n\n
The value <\/strong>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.<\/p>\n\n\n\n
The is_called<\/strong> 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 (<\/strong>is_called<\/code>)<\/strong>, PostgreSQL assumes the default value, which is true.<\/p>\n\n\n\n
<\/a>Summary<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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!<\/p>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":341597,"featured_media":104264,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,145792,143534],"tags":[159268,5854,158978],"coauthors":[158988],"class_list":["post-104261","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-mysql","category-postgresql","tag-aishabukar_mysql_postgresql","tag-mysql","tag-postgresql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104261","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341597"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104261"}],"version-history":[{"count":17,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104261\/revisions"}],"predecessor-version":[{"id":105553,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104261\/revisions\/105553"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104264"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104261"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}