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 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.

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.

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 type in both databases lets you define a list of allowed values for a column. We’ll explore how each database handles ENUM and what makes them different.

MySQL also has a SET type, which lets you store multiple values from a predefined list in a single field. PostgreSQL doesn’t have a direct equivalent, but it has the ARRAY type, which lets you store a list of values in one column. Arrays in PostgreSQL are very flexible—you can search, pick specific parts, and do other operations on them. We’ll also take a closer look at how this works.

MySQL and PostgreSQL offer a variety of string data types we can implement into our databases. Let’s dive in and explore how these string data types work in both of these powerful database systems.

Note: One thing I am not covering in this article, that is very important, is character sets and collations. That was covered in the previous article here.

MySQL String Data Types

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.

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:

VARCHAR(n), CHAR(n), and the TEXT 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:

Data type discussion

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.

When is VARCHAR(n) Better?

The VARCHAR(n) 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 or LONGTEXT datatypes, which I will note later in this section).

The VARCHAR(n) 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.

The VARCHAR data type also only uses as much space as the actual text needs (based on the length of the data). So, it doesn’t waste storage space to make this a variable length column.

When is CHAR(n) Better?

CHAR(n) 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.

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.

What about TEXT data types?

While the base CHAR and VARCHAR 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:

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

What is interesting about these is that TINYTEXT has the same max number of characters as a CHAR type, and TEXT and VARCHAR 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 does not incur this cost.)

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

Indexes on TEXT 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 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 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.

That’s why the suggestion is to use `TEXT` 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 data type in both MySQL and PostgreSQL:

MySQL:

PostgreSQL:

You can retrieve data the same way in both databases using the same query:

THE BLOB TYPES

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, BLOB, MEDIUMBLOB, and LONGBLOB.

Note: While BLOB types are not strictly string types, you can use them to hold string values in their binary form.

Let’s look at a quick demo to show how you can use BLOB to store small binary files, like images in MySQL. Firstly, we start out by creating a table:

The next step is to run the following command in MySQL to check the secure_file_priv setting. This helps us verify the path where our image file should be stored.

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.

Now, use the LOAD_FILE function to insert the image data into the BLOB column:

Use a SELECT statement to view the table

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.

Enumerated Data Type (ENUM) in MySQL

In MySQL, the ENUM 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.

Example:

Let’s create a table that uses an ENUM for the status column in a tasks table.

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:

This is what the output looks like.

If you try to insert a value that isn’t in the ENUM list:

MySQL will return an error:

Error Code: 1265 Data truncated for column ‘status’ at row 1

This is because ‘done’ is not in the ENUM list, hence, the data cannot be added to the column.

We can also just retrieve a specific data from our table using our SELECT statement. For example:

This is what the output looks like.

SET Data Type in MySQL

The SET data type in MySQL is used to store multiple string values from a predefined list of options. Unlike ENUM, which only allows a single value, SET allows you to select zero, one, or more values from the list.

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.

Example:

First, I will create a table with a SET column:

The preferences column can store any combination of the three defined options. As an example, I will insert values into the SET column that match the data SET values:

Retrieve your data using a SELECT statement

SELECT * FROM user_preferences;

The first statement inserts multiple values into the row thanks to the SET data type. It also allows you to insert a single value into the SET column and also no value. Yes, no value. When no value is inserted into the SET column, it returns an empty string. An empty string is not the same as NULL.

An empty string is a valid string value, it is a string with no characters. NULL indicates the absence of a value or an unknown value. If you want to explicitly store NULL for a column, you would use the keyword NULL in the VALUES list. This stores a NULL value indicating the absence of a value or an unknown value.

Then in the table you would see a NULL for the row with user_id = 4.

Even though SET 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’s check out string data types in Postgres!

PostgreSQL String Data Type

In the following table is a list of the various types of string datatypes that are available in PostgreSQL.

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` data type in PostgreSQL and see how it stacks up against the binary string data types in MySQL.

In PostgreSQL, the `BYTEA` 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.

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.

ENUM (Enumerated Data Type) in PostgreSQL

The ENUM 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.

Example Use Case:

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, you can define these possible statuses by:

First, we define the order_status type with the allowed values: ‘pending’, ‘shipped’, and ‘delivered’.

Now, let’s create a table called orders, where the status column uses the order_status type.

We can now insert data into the orders table, specifying the status from our ENUM type.

To check the inserted data, you can simply run a SELECT query.

This would output something like this:

ARRAY Data Type in PostgreSQL

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.

Example Use Case:

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’s how you can do this:

Create a table and a courses column to hold an array of course names, such as ‘Math’, ‘History’, ‘Science’.

Insert data into the array, and then output the contents:

The output would be (though the id value may be different):

This shows a list of the courses John Doe takes as a student.

The main difference between an `ENUM` and an `ARRAY` is that an `ENUM` lets you pick just one value from a set list of options, while an `ARRAY` allows you to store any value of the same type (like a list) in a single column.

BYTEA Data Type

Sometimes, applications may be required to store images that are uploaded by users, the BYTEA data type can help you do that.

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

PostgreSQL’s 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.

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….

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.

Here’s an example on working with the BYTEA data type:

We will insert binary data in an hexadecimal format which represents the text “Hello!” into the BYTEA

The output with be a binary value:

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:

Then the output would be:

BYTEA is useful for many uses, but may not be the perfect choice when storing very large binary objects. PostgreSQL’s large object is often a better choice. Although the large object is not a data type, it is a bit similar to how the BLOB types in MYSQL work, but a bit more complicated to use. PostgreSQL’s official documentation sheds more light on how to use the large object.

Let’s take a look at a brief comparison of the BYTEA data type in PostgreSQL and the BLOB data types in MySQL:

MySQL vs PostgreSQL: Comparison of String Data Types

ENUM (MySQL) Vs. ENUM (PostgreSQL)

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’s take a look at how this works:

MySQL

In MySQL, you can define ENUM directly within the column definition when creating a table

Once defined, modifying the ENUM list (like adding new options) requires altering the table. MySQL does not allow direct changes to the ENUM changes, so here’s how it supports adding new options:

Let’s verify the changes using the following command:

PostgreSQL

In PostgreSQL, ENUM 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.

Then you can use the ENUM this way:

PostgreSQL allows you to add new values to the ENUM type with a simple command

This means PostgreSQL is slightly more flexible when modifying ENUM 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 covers it quite well)

SET(MySQL) Vs. ARRAY(PostgreSQL)

In MySQL and PostgreSQL, SET and ARRAY data types both allow you to store multiple values in a single column, but they work differently:

MySQL’s `SET` 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` type stores multiple values without a fixed list, allowing you to add any values of the same data type without restrictions.

If you want to add or remove options in your SET, you have to change the column setup in the database but in PostgreSQL’s ARRAY, you can add or remove values as needed without having to adjust the structure of the database column.

MySQL’s SET works well when you have a small, fixed list of choices, like “small,” “medium,” “large while PostgreSQL’s ARRAY is useful for storing a list of values that may change frequently, like a list of tags or categories that can vary.

Key Takeaways

MySQL offers fixed-length (CHAR) and variable-length (VARCHAR) types, with a simpler range but clear limits, while PostgreSQL provides more flexibility with CHARACTER, CHARACTER VARYING, and related options. PostgreSQL’s string handling is more robust, with larger size limits (up to 1GB for CHAR and 10MB+ for VARCHAR), making it better for large or complex datasets.

MySQL breaks down text storage into multiple types (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT), offering granular control over storage size. PostgreSQL simplifies this with a single TEXT type, which can handle data up to 1GB. This simplicity makes PostgreSQL’s approach cleaner and easier to manage for text-heavy applications.

MySQL provides a variety of binary storage types (BLOB, VARBINARY, TINYBLOB, etc.), allowing for optimized storage of binary data of different sizes. PostgreSQL, on the other hand, uses just one type (BYTEA), which is straightforward but highly capable, with a maximum size of 1GB.

Both databases support enumerated types (ENUM), but PostgreSQL also includes arrays (ARRAY), while MySQL offers sets (SET). PostgreSQL’s ARRAY provides more advanced data structuring, whereas MySQL’s SET is a simpler solution for predefined collections.

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.