{"id":105620,"date":"2025-03-20T22:05:00","date_gmt":"2025-03-20T22:05:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=105620"},"modified":"2025-02-13T22:17:24","modified_gmt":"2025-02-13T22:17:24","slug":"mysql-vs-postgresql-string-datatypes","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/mysql-vs-postgresql-string-datatypes\/","title":{"rendered":"MySQL vs PostgreSQL \u2013 String Datatypes"},"content":{"rendered":"\n

A very common task in creating a database is to store string data. For example, words, paragraph(s) or even documents. String data types allow you to do just that and store and represent text. They handle everything from simple names and addresses to complex data.<\/p>\n\n\n\n

A string is simply a sequence of characters. These characters can be letters, numbers, symbols, or even spaces. For example, “Simple Talk”, “MySQL and PostgreSQL”, “1234” are all strings. Think of each character as a building block. A string is made up of these blocks, arranged in a specific order.<\/p>\n\n\n\n

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

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

String data types are important in database management systems. Let’s say you are trying to store some user information in a table. You want to be able to handle values of varying lengths, for example email addresses that can accommodate different email lengths. Some values will have a fixed length, like identification numbers. There are also varying characters you may need so store and deal with from different languages and today, even emojis.<\/p>\n\n\n\n

In addition to standard string types, MySQL and PostgreSQL offer special string data types that help structure data in specific ways. For example, the ENUM<\/code> type in both databases lets you define a list of allowed values for a column. We\u2019ll explore how each database handles ENUM<\/code> and what makes them different.<\/p>\n\n\n\n

MySQL also has a SET<\/code> type, which lets you store multiple values from a predefined list in a single field. PostgreSQL doesn\u2019t have a direct equivalent, but it has the ARRAY<\/code> type, which lets you store a list of values in one column. Arrays in PostgreSQL are very flexible\u2014you can search, pick specific parts, and do other operations on them. We\u2019ll also take a closer look at how this works.<\/p>\n\n\n\n

MySQL and PostgreSQL offer a variety of string data types we can implement into our databases. Let\u2019s dive in and explore how these string data types work in both of these powerful database systems.<\/p>\n\n\n\n

Note: One thing I am not covering in this article, that is very important, is character sets and collations. That was covered in <\/em>the previous article here<\/em><\/a>.<\/em><\/p>\n\n\n\n

<\/a>MySQL String Data Types<\/h2>\n\n\n\n

MySQL DBMS provides a range of string data types for fixed-length and variable-length strings, ranging from a single character to very large text storage. Knowing when to choose the right string data type is important because it affects how your text is stored and how fast your database can retrieve and process it.<\/p>\n\n\n\n

In this section, we will go into details of the string data types available in MySQL while also highlighting their characteristics and functionalities. Here is a complete breakdown:<\/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

CHAR(n)<\/strong><\/p>\n<\/td>

\n

It is a fixed-length character string that is filled with spaces to the defined length n.<\/p>\n<\/td>

\n

For storing codes with a fixed number of characters.<\/p>\n<\/td><\/tr>

\n

VARCHAR(n)<\/strong><\/p>\n<\/td>

\n

It is a variable-length character string that stores only the actual length of the text, up to a maximum length n.<\/p>\n<\/td>

\n

It is more efficient for most use cases when you know the maximum length of the text (e.g., names, emails, or usernames).<\/p>\n<\/td><\/tr>

\n

TINYTEXT<\/strong><\/p>\n<\/td>

\n

A small text string capable of storing up to 255 characters.<\/p>\n<\/td>

\n

Comments or small notes. Typically items you don\u2019t want to sort on.<\/p>\n<\/td><\/tr>

\n

TEXT<\/strong><\/p>\n<\/td>

\n

It is a standard text string, capable of storing up to 65,535 bytes (about 64 KB).<\/p>\n<\/td>

\n

Use TEXT<\/code> for very large, unpredictable text (e.g., blog posts, comments), but be aware it may have slower performance for queries and sorting.<\/p>\n<\/td><\/tr>

\n

MEDIUMTEXT<\/strong><\/p>\n<\/td>

\n

It is a medium-length text string, capable of storing up to 16,777,215 bytes (about 16 MB).<\/p>\n<\/td>

\n

Use MEDIUMTEXT<\/code> when TEXT<\/code> isn\u2019t enough but LONGTEXT<\/code> is excessive such as longer blog posts or articles.<\/p>\n<\/td><\/tr>

\n

LONGTEXT<\/strong><\/p>\n<\/td>

\n

It is a large text string, capable of storing up to 4,294,967,295 bytes (about 4 GB).<\/p>\n<\/td>

\n

Use for very large text, like entire documents or logs, with a maximum size of 4 GB<\/p>\n<\/td><\/tr>

\n

BINARY(n)<\/strong><\/p>\n<\/td>

\n

It is a fixed-length binary data, like CHAR but for non-text data.<\/p>\n<\/td>

\n

Storing encrypted keys.<\/p>\n<\/td><\/tr>

\n

VARBINARY(N)<\/strong><\/p>\n<\/td>

\n

It is a variable-length binary data, like VARCHAR but for non-text data.<\/p>\n<\/td>

\n

Storing files, Images.<\/p>\n<\/td><\/tr>

\n

TINYBLOB<\/strong><\/p>\n<\/td>

\n

It is a binary large object that stores data up to 255 bytes.<\/p>\n<\/td>

\n

For storing small binary files, like icons<\/p>\n<\/td><\/tr>

\n

BLOB<\/strong><\/p>\n<\/td>

\n

It is a binary large object that stores data, up to 65,535 bytes.<\/p>\n<\/td>

\n

For storing audio files, images.<\/p>\n<\/td><\/tr>

\n

MEDIUMBLOB<\/strong><\/p>\n<\/td>

\n

It is a binary large object that stores data, up to 16777215 bytes (16 MB)<\/p>\n<\/td>

\n

Storing multimedia files, like videos.<\/p>\n<\/td><\/tr>

\n

LONGBLOB<\/strong><\/p>\n<\/td>

\n

It is a very large binary object that stores data, up to 4 billion bytes.<\/p>\n<\/td>

\n

Storing high quality movies, documentaries.<\/p>\n<\/td><\/tr>

\n

ENUM<\/strong><\/p>\n<\/td>

\n

A text that can only be one value from a list you define.<\/p>\n<\/td>

\n

Storing categorical data, such as gender, status.<\/p>\n<\/td><\/tr>

\n

SET<\/strong><\/p>\n<\/td>

\n

A text that can hold one or more values from a list you define.<\/p>\n<\/td>

\n

Storing multiple options, like colours.<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

VARCHAR(n)<\/code>, CHAR(n)<\/code>, and the TEXT<\/code> datatypes are ways to store text in a database, but they work differently and are useful for different situations. Here’s an easy way to understand their differences:<\/p>\n\n\n\n

<\/a>Data type discussion<\/h2>\n\n\n\n

In this section I am going to cover a bit of why you might choose one type or another for different uses. Some of the datatypes are so similar, but there are internal differences that dictate why you might choose one over the other.<\/p>\n\n\n\n

When is VARCHAR(n) Better?<\/h3>\n\n\n\n

The VARCHAR(n)<\/code> data type in MySQL is used to store variable-length text up to 65,535 bytes. (If you need more, you can use the MEDIUMTEXT<\/code> or LONGTEXT<\/code> datatypes, which I will note later in this section).<\/p>\n\n\n\n

The VARCHAR(n)<\/code> data type can hold very large pieces of text, up to 65,535 characters (about 64 KB). In can be indexed, but the max key length for an index is 1017 bytes.<\/p>\n\n\n\n

The VARCHAR<\/code> data type also only uses as much space as the actual text needs (based on the length of the data). So, it doesn\u2019t waste storage space to make this a variable length column.<\/p>\n\n\n\n

<\/a>When is CHAR(n) Better?<\/h3>\n\n\n\n

CHAR(n)<\/code> is good for fixed-length text values. It has a maximum length of 255 characters and is ideal when you know exactly how long the text will always be, like country codes (“USA”, “CAN”) or short answers (“YES”, “NO”). It is best used when the data is exactly the length of the datatype, or very close to the length.<\/p>\n\n\n\n

It’s not particularly great for things like long descriptions or notes because you need to know the exact size of the text in advance.<\/p>\n\n\n\n

<\/a>What about TEXT data types?<\/h3>\n\n\n\n

While the base CHAR<\/code> and VARCHAR<\/code> datatypes are the datatypes you most typically will use for storing textual data, there are also a set of data types that are hold string data that are similar, but different than those data types:<\/p>\n\n\n\n

In MySQL, the TEXT<\/code> data types are used to store large amounts of text data. There are four main types as were described in the table earlier: TINYTEXT<\/code>, TEXT<\/code>, MEDIUMTEXT<\/code>, and LONGTEXT<\/code>. Each has a max amount of text it can hold.<\/p>\n\n\n\n

What is interesting about these is that TINYTEXT<\/code> has the same max number of characters as a CHAR<\/code> type, and TEXT<\/code> and VARCHAR<\/code> have the same upper bounds and use only as much space as the actual text requires (plus a 1 or 2 byte overhead to capture the size of the value being stored for variable types. CHAR<\/code> does not incur this cost.)<\/p>\n\n\n\n

The biggest difference between these different types of TEXT<\/code> is that they are not manipulated in the database server\u2019s memory in the same way that a VARCHAR<\/code> value is. So, searching for values stored in a TINYTEXT<\/code> column can be costly compared to a VARCHAR(255)<\/code>.<\/p>\n\n\n\n

Indexes on TEXT<\/code> columns are possible, but you are required to always use a fixed amount of space, and they require a prefix index to do so. So if you have a column that is declared name TINYTEXT<\/code> for example, to add an index, you would need to specify how much of the 255 bytes you want to have indexed, such as name(200). Beware that indexes on any of the TEXT<\/code> types behave like a fixed length value, using all of the bytes for every index key value. So every index key value for the name(200) index would use 200 bytes.<\/p>\n\n\n\n

That’s why the suggestion is to use `TEXT<\/code>` for values that are rarely searched or sorted, such as addresses or comments. Here’s how you can store and retrieve text strings using the TEXT<\/code> data type in both MySQL and PostgreSQL:<\/p>\n\n\n\n

MySQL:<\/p>\n\n\n\n

CREATE TABLE messages (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    content TEXT\n);\n\nINSERT INTO messages (content) \nVALUES ('Hello, this is a sample message.');<\/pre>\n\n\n\n
PostgreSQL:<\/p>\n\n\n\n
CREATE TABLE messages (\n    id SERIAL PRIMARY KEY,\n    content TEXT\n);\n\nINSERT INTO messages (content) \nVALUES ('Hello, this is a sample message.');<\/pre>\n\n\n\n
You can retrieve data the same way in both databases using the same query:<\/p>\n\n\n\n
SELECT content \nFROM messages WHERE id = 1;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a><\/a><\/a>THE BLOB TYPES<\/h3>\n\n\n\n
The BLOB type is designed for storing binary data, such as files, images, audio, videos, or any non-text content in binary format, meaning it is treated as raw bytes, with no character encoding. The BLOB type in MySQL comes in four variants, which is, TINYBLOB<\/code>, BLOB<\/code>, MEDIUMBLOB<\/code>, and LONGBLOB<\/code>.<\/p>\n\n\n\n
Note: While BLOB types are not strictly string types, you can use them to hold string values in their binary form.<\/em><\/p>\n\n\n\n
Let\u2019s look at a quick demo to show how you can use BLOB<\/code> to store small binary files, like images in MySQL. Firstly, we start out by creating a table:<\/p>\n\n\n\n
CREATE TABLE icons (\n    id INT PRIMARY KEY,\n    name VARCHAR(50),\n    data BLOB\n);<\/pre>\n\n\n\n
The next step is to run the following command in MySQL to check the secure_file_priv<\/code> setting. This helps us verify the path where our image file should be stored.<\/p>\n\n\n\n
SHOW VARIABLES LIKE 'secure_file_priv';<\/pre>\n\n\n\n
The following command should return a directory path similar to the image below, you will need to copy and paste the image into this directory.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Now, use the LOAD_FILE<\/code> function to insert the image data into the BLOB column:<\/p>\n\n\n\n
INSERT INTO icons (id, name, data) \nVALUES (2, 'example_icon', \n   LOAD_FILE('C:\/ProgramData\/MySQL\/MySQL Server 9.0\/Uploads\/icon.png'));<\/pre>\n\n\n\n
Use a SELECT<\/code> statement to view the table<\/p>\n\n\n\n
SELECT name, data FROM icons WHERE id = 2;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This is what your output should look like in your MySQL database. If we want to be more practical and try to display the blob data (image), we will need to use a Programming language like Python or JavaScript.<\/p>\n\n\n\n
Enumerated Data Type (ENUM) in MySQL<\/h3>\n\n\n\n
In MySQL, the ENUM<\/code> data type allows you to define a column that can only hold one value from a predefined list of options. This is useful for storing things like status values, categories, or other fixed sets of data.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
Let’s create a table that uses an ENUM<\/code> for the status column in a tasks table.<\/p>\n\n\n\n
CREATE TABLE tasks (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(255),\n    status ENUM('pending', 'in_progress', 'completed') NOT NULL\n);<\/pre>\n\n\n\n
In this example, the status column can only have one of the three values: ‘pending’, ‘in_progress’, or ‘completed’. You can insert values into the table using one of the predefined values:<\/p>\n\n\n\n
INSERT INTO tasks (name, status) \nVALUES ('Complete documentation', 'in_progress'),\n       ('Complete API documentation', 'in_progress'), \n       ('Test APIs', 'completed');\n\nSELECT * FROM tasks;<\/pre>\n\n\n\n
This is what the output looks like.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
If you try to insert a value that isn’t in the ENUM<\/code> list:<\/p>\n\n\n\n
INSERT INTO tasks (name, status) \nVALUES ('Submit report', 'done');<\/pre>\n\n\n\n
MySQL will return an error:<\/p>\n\n\n\n
Error Code: 1265 Data truncated for column \u2018status\u2019 at row 1<\/code><\/p>\n\n\n\n
This is because ‘done’ is not in the ENUM<\/code> list, hence, the data cannot be added to the column.<\/p>\n\n\n\n
We can also just retrieve a specific data from our table using our SELECT statement. For example:<\/p>\n\n\n\n
SELECT * \nFROM tasks \nWHERE status = 'completed';<\/pre>\n\n\n\n
This is what the output looks like.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SET Data Type in MySQL<\/h3>\n\n\n\n
The SET<\/code> data type in MySQL is used to store multiple string values from a predefined list of options. Unlike ENUM<\/code>, which only allows a single value, SET<\/code> allows you to select zero, one, or more values from the list.<\/p>\n\n\n\n
It is similar to an array, but only allows string values in MySQL. This comes in handy when you need to represent multiple choices in a single column.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n\n
First, I will create a table with a SET<\/code> column:<\/p>\n\n\n\n
CREATE TABLE user_preferences (\n    user_id INT PRIMARY KEY,\n    preferences SET('email_notifications', \n                    'sms_notifications', 'push_notifications')\n);<\/pre>\n\n\n\n
The preferences column can store any combination of the three defined options. As an example, I will insert values into the SET<\/code> column that match the data SET<\/code> values:<\/p>\n\n\n\n
INSERT INTO user_preferences (user_id, preferences) \nVALUES \n(1, 'email_notifications,sms_notifications'), -- Multiple values\n(2, 'push_notifications'),                    -- Single value\n(3, '');                                      -- No value<\/pre>\n\n\n\n
Retrieve your data using a SELECT statement<\/p>\n\n\n\n
SELECT * FROM user_preferences;<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The first statement inserts multiple values into the row thanks to the SET<\/code> data type. It also allows you to insert a single value into the SET<\/code> column and also no value. Yes, no value. When no value is inserted into the SET<\/code> column, it returns an empty string. An empty string is not the same as NULL<\/code>.<\/p>\n\n\n\n
An empty string is a valid string value, it is a string with no characters. NULL<\/code> indicates the absence of a value or an unknown value. If you want to explicitly store NULL<\/code> for a column, you would use the keyword NULL<\/code> in the VALUES<\/code> list. This stores a NULL<\/code> value indicating the absence of a value or an unknown value.<\/p>\n\n\n\n
INSERT INTO user_preferences (user_id, preferences)\nVALUES (4, NULL); <\/pre>\n\n\n\n
Then in the table you would see a NULL<\/code> for the row with user_id = 4<\/code>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Even though SET<\/code> is efficient for storing multiple options, its use is limited to predefined lists, and modifications to the list (like adding or removing values) require altering the table. Now, let\u2019s check out string data types in Postgres!<\/p>\n\n\n\n
<\/a>PostgreSQL String Data Type<\/h2>\n\n\n\n
In the following table is a list of the various types of string datatypes that are 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
CHARACTER(n)<\/strong><\/p>\n
Or CHAR(n)<\/strong><\/p>\n<\/td>
\n
A fixed-length character string. If the input is shorter than n, it is padded with spaces.<\/p>\n<\/td>
\n
For storing country codes, area codes, etc.<\/p>\n<\/td><\/tr>
\n
CHARACTER VARYING(n) <\/strong><\/p>\n
Or VARCHAR(n)<\/strong><\/p>\n<\/td>
\n
A variable-length character string, with a maximum length of n. Only stores actual input length.<\/p>\n<\/td>
\n
For storing text fields with variable content length (e.g., names, emails, addresses), but with a defined maximum size.<\/p>\n<\/td><\/tr>
\n
BPCHAR(n)<\/strong><\/p>\n<\/td>
\n
Internal name for CHAR(n)<\/code> or CHARACTER(n)<\/code>. Stands for “blank-padded CHAR”.<\/p>\n<\/td>
\n
Same as CHARACTER(n)<\/code> or CHAR(n)<\/code> – fixed-length strings where space padding is allowed.<\/p>\n<\/td><\/tr>
\n
TEXT<\/strong><\/p>\n<\/td>
\n
A variable-length<\/strong> string with no defined maximum length. It can store any length of text.<\/p>\n<\/td>
\n
Articles, descriptions, etc<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
In PostgreSQL, the binary data type `BYTEA` isn’t technically a string data type, but it is often compared to MySQL’s binary types. Binary data is held in a `BYTEA`<\/code> data type in PostgreSQL and see how it stacks up against the binary string data types in MySQL.<\/p>\n\n\n\n
\n
 Data Type <\/strong><\/p>\n<\/td>
\n
Name<\/strong><\/p>\n<\/td>
\n
 Description<\/strong><\/p>\n<\/td>
\n
 Common Use cases<\/strong><\/p>\n<\/td><\/tr>
\n
Binary Type<\/p>\n<\/td>
\n
BYTEA<\/code><\/p>\n<\/td>
\n
Binary data type for storing binary strings in variable length with a maximum of 1GB.<\/p>\n<\/td>
\n
Storing binary files like images, documents, or any raw binary data.<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
In PostgreSQL, the `BYTEA`<\/code> data type is used for storing binary data (like images, files, or other non-text data). It’s similar to storing raw bytes in a column.<\/p>\n\n\n\n
When inserting or selecting `BYTEA` data, it is typically helpful to encodes it in a specific format (like hexadecimal or escape format when inserting data as a string value in code) to ensure its stored correctly. It’s particularly useful for handling large binary files like images, videos, or encrypted data in a structured database system.<\/p>\n\n\n\n
<\/a>ENUM (Enumerated Data Type) in PostgreSQL<\/h3>\n\n\n\n
The ENUM<\/code> data type allows you to define a list of predefined values. Each value represents a specific state or category, and only one of those values can be stored in a column at a time. This is useful when you want to limit the values a column can accept to a fixed set.<\/p>\n\n\n\n
Example Use Case:<\/strong><\/p>\n\n\n\n
You have a table for orders, and the status of each order can only be one of a few values like ‘pending’, ‘shipped’, or ‘delivered’. Using ENUM<\/code>, you can define these possible statuses by:<\/p>\n\n\n\n
First, we define the order_stat<\/code>us type with the allowed values: ‘pending’, ‘shipped’, and ‘delivered’.<\/p>\n\n\n\n
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered');<\/pre>\n\n\n\n
Now, let’s create a table called orders, where the status column uses the order_status type.<\/p>\n\n\n\n
CREATE TABLE orders (\n    order_id SERIAL PRIMARY KEY,\n    customer_name TEXT,\n    status order_status DEFAULT 'pending' -- Default status is 'pending'\n);<\/pre>\n\n\n\n
We can now insert data into the orders table, specifying the status from our ENUM<\/code> type.<\/p>\n\n\n\n
INSERT INTO orders (customer_name, status)  \nVALUES   ('John Doe', 'shipped'), \n         ('Jane Smith', 'delivered'), \n         ('Emily Johnson', 'pending'),  \n         ('Aisha Bukar', DEFAULT);  <\/pre>\n\n\n\n
To check the inserted data, you can simply run a SELECT<\/code> query.<\/p>\n\n\n\n
SELECT * FROM orders;<\/pre>\n\n\n\n
This would output something like this:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a>ARRAY Data Type in PostgreSQL<\/h3>\n\n\n\n
PostgreSQL also allows you to store arrays of data in a single column. You can store multiple values of a single data type (like integers or text) within one array column. This is useful when you need to store lists of items related to a single row, without creating a separate table.<\/p>\n\n\n\n
Example Use Case:<\/strong><\/p>\n\n\n\n
If you have a table of students and you want to store a list of the courses they’re enrolled in for each student, you could use an array to hold all the course names in one column. Here\u2019s how you can do this:<\/p>\n\n\n\n
Create a table and a courses column to hold an array of course names, such as ‘Math’, ‘History’, ‘Science’.<\/p>\n\n\n\n
CREATE TABLE students (\n    id SERIAL PRIMARY KEY,\n    name TEXT,\n    courses TEXT[]\n);<\/pre>\n\n\n\n
Insert data into the array, and then output the contents:<\/p>\n\n\n\n
INSERT INTO students (name, courses) \nVALUES ('John Doe', ARRAY['Math', 'History', 'Science']);\n\nSELECT * FROM students;<\/pre>\n\n\n\n
The output would be (though the id value may be different):<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This shows a list of the courses John Doe takes as a student.<\/p>\n\n\n\n
The main difference between an `ENUM`<\/code> and an `ARRAY`<\/code> is that an `ENUM`<\/code> lets you pick just one<\/strong> value from a set list of options, while an `ARRAY`<\/code> allows you to store any<\/strong> value of the same type (like a list) in a single column.<\/p>\n\n\n\n
<\/a>BYTEA Data Type<\/h3>\n\n\n\n
Sometimes, applications may be required to store images that are uploaded by users, the BYTEA<\/code> data type can help you do that.<\/p>\n\n\n\n
The BYTEA data type is used to store binary data, such as images or other non-text files. It can store binary data of up to 1GB, which is about 1024 MB in a single column<\/p>\n\n\n\n
PostgreSQL\u2019s BYTEA supports two ways to format this binary data: Hex format and Escape format. Hex format uses hexadecimal (hex) values, which are characters like 0-9 and A-F.<\/p>\n\n\n\n
Binary data in hex format looks cleaner and is often easier to understand, especially for data that could include special characters. For instance, the hex value for a small image file might start like this: \\x89504e47….<\/p>\n\n\n\n
The escape format is an older style and is now less common, but it can still be used. In escape format, binary data is represented by escape sequences, using backslashes (\\) to mark special bytes. For instance, a byte might be shown as \\201.<\/p>\n\n\n\n
Here\u2019s an example on working with the BYTEA data type:<\/p>\n\n\n\n
CREATE TABLE messages (\n    id SERIAL PRIMARY KEY,       \n    message_data BYTEA           \n);<\/pre>\n\n\n\n
We will insert binary data in an hexadecimal format which represents the text “Hello!” into the BYTEA<\/code><\/p>\n\n\n\n
INSERT INTO messages (message_data) \nVALUES (E'\\\\x48656C6C6F21');\n\nSELECT message_data FROM messages;<\/pre>\n\n\n\n
The output with be a binary value:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This is what the output looks like. You can use PostgreSQL’s convert_from() function to decode the binary data into a human-readable text format:<\/p>\n\n\n\n
SELECT convert_from(message_data, 'UTF8') AS readable_message 
FROM messages;<\/pre>\n\n\n\n
Then the output would be:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
BYTEA is useful for many uses, but may not be the perfect choice when storing very large binary objects. PostgreSQL\u2019s large object<\/a> is often a better choice. Although the large object is not a data type, it is a bit similar to how the BLOB<\/code> types in MYSQL work, but a bit more complicated to use. PostgreSQL\u2019s official documentation sheds more light on how to use the large object<\/a>.<\/p>\n\n\n\n
Let\u2019s take a look at a brief comparison of the BYTEA data type in PostgreSQL and the BLOB data types in MySQL:<\/a><\/p>\n\n\n\n

Feature<\/strong><\/p>
<\/td>

BLOB<\/strong><\/p>
<\/td>

BYTEA<\/strong><\/p>
<\/td><\/tr>
\n
Size Limit<\/p>\n<\/td>
\n
It can store binary size greater than 1GB.<\/p>\n<\/td>
\n
It has a maximum size of 1GB.<\/p>\n<\/td><\/tr>
\n
Ease of Use<\/p>\n<\/td>
\n
To function properly, it requires the use of some APIs or special functions.<\/p>\n<\/td>
\n
It is simple to use and works with standard SQL commands.<\/p>\n<\/td><\/tr>
\n
Use case<\/p>\n<\/td>
\n
Best for large binary objects.<\/p>\n<\/td>
\n
Better for smaller binary objects.<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
MySQL vs PostgreSQL: Comparison of String Data Types<\/h2>\n\n\n\n
\n
Data Type <\/strong><\/p>\n<\/td>
\n
MySQL<\/strong><\/p>\n<\/td>
\n
 Range (MySQL)<\/strong><\/p>\n<\/td>
\n
PostgreSQL<\/strong><\/p>\n<\/td>
\n
Range (PostgreSQL)<\/strong><\/p>\n<\/td>
\n
Differences<\/strong><\/p>\n<\/td><\/tr>
\n
String types<\/p>\n<\/td>
\n
\u2018CHAR(n)\u2019, \u2019VARCHAR(n)\u2019<\/p>\n<\/td>
\n
Maximum VARCHAR length: 65535<\/p>\n
Maximum CHAR length: 255<\/p>\n<\/td>
\n
‘CHARACTER(n)’, ‘CHARACTER VARYING(n)’, ‘CHAR(n)’, ‘BPCHAR(n)’ ‘VARCHAR(n)’<\/p>\n<\/td>
\n
Maximum CHARACTER VARYING(n) and VARCHAR(n) length: 10485760<\/p>\n
‘CHARACTER(n)\u2019, \u2018CHAR(n)\u2019: 1GB<\/p>\n<\/td>
\n
Fixed-length types (CHAR) can be faster but may waste space.<\/p>\n
Variable-length types (VARCHAR) are more storage-efficient.<\/p>\n
PostgreSQL’s CHARACTER and CHARACTER VARYING handle variable-length strings efficiently.<\/p>\n<\/td><\/tr>
\n
Text types<\/p>\n<\/td>
\n
\u2018TINYTEXT\u2019, \u2018TEXT\u2019, \u2018MEDIUMTEXT\u2019, \u2018LONGTEXT\u2019<\/p>\n<\/td>
\n
\u2018TINYTEXT\u2019 maximum length: 255(28<\/sup>-1)<\/p>\n
\u2018TEXT\u2019 maximum length: 65,535<\/p>\n
\u2018MEDIUMTEXT\u2019 maximum length: 16,777,215<\/p>\n
\u2018LONGTEXT\u2019 maximum length: 4,294,967,295<\/p>\n<\/td>
\n
\u2018TEXT\u2019<\/p>\n<\/td>
\n
Maximum length: 1GB<\/p>\n<\/td>
\n
MySQL provides multiple text types for different storage needs, while PostgreSQL uses a single TEXT type.<\/p>\n<\/td><\/tr>
\n
Binary types<\/p>\n<\/td>
\n
\u2018BINARY\u2019, \u2018VARBINARY\u2019, \u2018TINYBLOB\u2019, \u2018BLOB\u2019, \u2018MEDIUMBLOB\u2019, \u2018LONGBLOB\u2019<\/p>\n<\/td>
\n
BINARY: Fixed-length binary string up to 255 bytes.<\/p>\n
VARBINARY: Variable-length binary string up to 65,535 bytes.<\/p>\n
TINYBLOB: Binary large object up to 255 bytes.<\/p>\n
BLOB: Binary large object up to 65,535 bytes.<\/p>\n
MEDIUMBLOB: Binary large object up to 16,777,215 bytes (16 MB).<\/p>\n
LONGBLOB: Binary large object up to 4,294,967,295 bytes (4 GB).<\/p>\n<\/td>
\n
\u2018BYTEA\u2019<\/p>\n<\/td>
\n
Maximum limit: 1GB<\/p>\n<\/td>
\n
MySQL offers various binary types while PostgreSQL uses only BYTEA for storing binary data.<\/p>\n<\/td><\/tr>
\n
Special types<\/p>\n<\/td>
\n
\u2018ENUM\u2019, \u2018SET\u2019<\/p>\n<\/td>
\n
Maximum length of ENUM: Defined by the enumeration.<\/p>\n
Maximum length of SET: 65,535 bytes<\/p>\n<\/td>
\n
\u2018ENUM\u2019, \u2018ARRAY\u2019<\/p>\n<\/td>
\n
Maximum length of ENUM: Defined by the enumeration.<\/p>\n
Maximum length of ARRAY: Defined by the array’s base type length.<\/p>\n<\/td>
\n
Both databases support enumerated types (ENUM).<\/p>\n
PostgreSQL offers ARRAY, while MySQL provides SET.<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
<\/a>ENUM (MySQL) Vs. ENUM (PostgreSQL)<\/h3>\n\n\n\n
In MySQL and PostgreSQL, the ENUM type is used to define a column that can only hold one value from a specific list of values. However, the way ENUM is implemented in each database is a bit different. Let\u2019s take a look at how this works:<\/p>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
In MySQL, you can define ENUM directly within the column definition when creating a table<\/p>\n\n\n\n
CREATE TABLE orders (\n    order_id INT,\n    status ENUM('pending', 'shipped', 'delivered')\n);<\/pre>\n\n\n\n
Once defined, modifying the ENUM<\/code> list (like adding new options) requires altering the table. MySQL does not allow direct changes to the ENUM<\/code> changes, so here\u2019s how it supports adding new options:<\/p>\n\n\n\n
ALTER TABLE orders \nMODIFY COLUMN status \n  ENUM('pending', 'shipped', 'delivered', 'canceled') NOT NULL;<\/pre>\n\n\n\n
Let\u2019s verify the changes using the following command:<\/p>\n\n\n\n
DESCRIBE orders;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
PostgreSQL<\/strong><\/p>\n\n\n\n
In PostgreSQL, ENUM<\/code> is created as a separate data type before it can be used in a table. You first define the type, then use it in a column.<\/p>\n\n\n\n
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered');<\/pre>\n\n\n\n
Then you can use the ENUM<\/code> this way:<\/p>\n\n\n\n
CREATE TABLE orders (\n    order_id INT,\n    status order_status\n);<\/pre>\n\n\n\n
PostgreSQL allows you to add new values to the ENUM<\/code> type with a simple command<\/p>\n\n\n\n
ALTER TYPE order_status ADD VALUE 'returned';<\/pre>\n\n\n\n
This means PostgreSQL is slightly more flexible when modifying ENUM<\/code> values. Note however, that removing an ENUM value is not supported, so you will have to remove the ENUM and replace it (while taking care to not lose data. For more information, this StackOverflow thread<\/a> covers it quite well)<\/p>\n\n\n\n
<\/a>SET(MySQL) Vs. ARRAY(PostgreSQL)<\/h3>\n\n\n\n
In MySQL and PostgreSQL, SET<\/code> and ARRAY<\/code> data types both allow you to store multiple values in a single column, but they work differently:<\/p>\n\n\n\n
MySQL’s `SET`<\/code> type stores multiple predefined options you specify in advance, so each stored value must come from this fixed list. On the other hand, PostgreSQL’s `ARRAY`<\/code> type stores multiple values without a fixed list, allowing you to add any values of the same data type without restrictions.<\/p>\n\n\n\n
If you want to add or remove options in your SET<\/code>, you have to change the column setup in the database but in PostgreSQL\u2019s ARRAY<\/code>, you can add or remove values as needed without having to adjust the structure of the database column.<\/p>\n\n\n\n
MySQL\u2019s SET<\/code> works well when you have a small, fixed list of choices, like “small,” “medium,” “large while PostgreSQL\u2019s ARRAY<\/code> is useful for storing a list of values that may change frequently, like a list of tags or categories that can vary.<\/p>\n\n\n\n
<\/a>Key Takeaways<\/h2>\n\n\n\n
MySQL offers fixed-length (CHAR<\/code>) and variable-length (VARCHAR<\/code>) types, with a simpler range but clear limits, while PostgreSQL provides more flexibility with CHARACTER<\/code>, CHARACTER<\/code> VARYING<\/code>, and related options. PostgreSQL’s string handling is more robust, with larger size limits (up to 1GB for CHAR<\/code> and 10MB+ for VARCHAR<\/code>), making it better for large or complex datasets.<\/p>\n\n\n\n
MySQL breaks down text storage into multiple types (TINYTEXT<\/code>, TEXT<\/code>, MEDIUMTEXT<\/code>, LONGTEXT<\/code>), offering granular control over storage size. PostgreSQL simplifies this with a single TEXT<\/code> type, which can handle data up to 1GB. This simplicity makes PostgreSQL’s approach cleaner and easier to manage for text-heavy applications.<\/p>\n\n\n\n
MySQL provides a variety of binary storage types (BLOB<\/code>, VARBINARY<\/code>, TINYBLOB<\/code>, etc.), allowing for optimized storage of binary data of different sizes. PostgreSQL, on the other hand, uses just one type (BYTEA<\/code>), which is straightforward but highly capable, with a maximum size of 1GB.<\/p>\n\n\n\n
Both databases support enumerated types (ENUM<\/code>), but PostgreSQL also includes arrays (ARRAY<\/code>), while MySQL offers sets (SET). PostgreSQL\u2019s ARRAY<\/code> provides more advanced data structuring, whereas MySQL\u2019s SET<\/code> is a simpler solution for predefined collections.<\/p>\n\n\n\n
Choose MySQL if you prefer a variety of types tailored for specific storage needs, especially for applications that benefit from granular control. Choose PostgreSQL if you need more flexibility, simplicity in managing text data, or advanced features like arrays and larger size limits.<\/p>\n","protected":false},"excerpt":{"rendered":"
A very common task in creating a database is to store string data. For example, words, paragraph(s) or even documents. String data types allow you to do just that and store and represent text. They handle everything from simple names and addresses to complex data. A string is simply a sequence of characters. These characters……<\/p>\n","protected":false},"author":341597,"featured_media":105636,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,145792,143534],"tags":[159268,5854,158978],"coauthors":[158988],"class_list":["post-105620","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","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\/105620","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=105620"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/105620\/revisions"}],"predecessor-version":[{"id":105644,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/105620\/revisions\/105644"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105636"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=105620"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=105620"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=105620"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=105620"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}