Splitting a Delimited String into Multiple Rows in PostgreSQL
Many PostgreSQL databases contain columns that store multiple values inside a single text field. These values are often separated by commas, semicolons, pipes (|), or other delimiters.
For example:
apple,banana,orange
Although storing multiple values in a single column is generally not recommended for relational database design, you'll frequently encounter it when importing CSV files, migrating legacy databases, consuming third-party APIs, or processing user-generated data.
Fortunately, PostgreSQL provides several built-in functions that make splitting delimited strings into individual rows straightforward and efficient.
This guide covers the most useful approaches, explains when to use each one, and highlights common mistakes to avoid.
What You'll Learn
After reading this guide, you'll understand:
- How to split strings into rows.
- The difference between
string_to_table()andregexp_split_to_table(). - When to use
unnest(). - How to preserve row relationships.
- Performance considerations.
- Best practices for production databases.
Sample Data
Suppose you have the following table:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
tags TEXT
);
INSERT INTO products (tags)
VALUES
('Laptop,Electronics,Portable'),
('Book,Education'),
('Phone,Gadget,Android');
Each row contains multiple values separated by commas.
Method 1: Using string_to_table()
PostgreSQL provides the string_to_table() function for splitting text directly into rows.
SELECT string_to_table(
'Laptop,Electronics,Portable',
','
);
Output
| string_to_table |
|---|
| Laptop |
| Electronics |
| Portable |
Advantages
- Simple syntax
- Fast execution
- Ideal for fixed delimiters
This is usually the best option when the separator is known and consistent.
Method 2: Using unnest() with string_to_array()
Another common solution is combining string_to_array() with unnest().
SELECT unnest(
string_to_array(
'Laptop,Electronics,Portable',
','
)
);
Output
| unnest |
|---|
| Laptop |
| Electronics |
| Portable |
This approach is especially useful when the array will be processed further.
Method 3: Using regexp_split_to_table()
If delimiters vary or include whitespace, regular expressions provide more flexibility.
SELECT regexp_split_to_table(
'Laptop, Electronics, Portable',
'\s*,\s*'
);
Output
| regexp_split_to_table |
|---|
| Laptop |
| Electronics |
| Portable |
This removes optional spaces around commas.
Splitting Pipe-Separated Values
Many legacy applications use the pipe character.
Example:
Apple|Orange|Banana
SQL:
SELECT string_to_table(
'Apple|Orange|Banana',
'|'
);
Splitting Semicolon-Separated Values
SELECT string_to_table(
'Red;Green;Blue',
';'
);
Result:
Red
Green
Blue
Splitting Values Stored in a Table
Instead of splitting a literal string, process every row.
SELECT
id,
string_to_table(tags, ',') AS tag
FROM products;
Result:
| id | tag |
|---|---|
| 1 | Laptop |
| 1 | Electronics |
| 1 | Portable |
| 2 | Book |
| 2 | Education |
| 3 | Phone |
| 3 | Gadget |
| 3 | Android |
Notice that each generated row retains the original record's ID.
Preserving Element Order
Sometimes order matters.
Use WITH ORDINALITY.
SELECT *
FROM string_to_table(
'A,B,C,D',
','
)
WITH ORDINALITY;
Result:
| Value | Position |
|---|---|
| A | 1 |
| B | 2 |
| C | 3 |
| D | 4 |
This is useful for ranked lists or ordered imports.
Splitting Multiple Columns
Suppose a table contains:
| id | categories |
|---|---|
| 1 | SQL,Python,AI |
Query:
SELECT
id,
category
FROM products
CROSS JOIN LATERAL
string_to_table(tags, ',') AS category;
This creates one output row for each value while preserving the relationship with the original record.
Removing Empty Values
Consider:
Apple,,Orange,,Banana
Use a filter:
SELECT value
FROM string_to_table(
'Apple,,Orange,,Banana',
','
) AS value
WHERE value <> '';
Result:
Apple
Orange
Banana
Removing Extra Spaces
Many imported files contain inconsistent spacing.
SELECT trim(value)
FROM string_to_table(
'Apple, Orange, Banana',
','
) AS value;
Output:
Apple
Orange
Banana
Using Regular Expressions for Multiple Delimiters
Suppose input contains commas and semicolons.
Apple,Banana;Orange,Grape
Use:
SELECT regexp_split_to_table(
'Apple,Banana;Orange,Grape',
'[,;]'
);
Output:
Apple
Banana
Orange
Grape
Converting Split Values into Integers
Sometimes IDs are stored as text.
Example:
10,25,30,45
Query:
SELECT
CAST(value AS INTEGER)
FROM string_to_table(
'10,25,30,45',
','
) AS value;
Now each value can participate in joins and numeric calculations.
Joining Split Values
Suppose you have:
user_roles
1,2,5
You can join:
SELECT
r.role_name
FROM string_to_table(
'1,2,5',
','
) AS role_id
JOIN roles r
ON r.id = role_id::INTEGER;
This converts delimited IDs into relational data.
Performance Considerations
For small datasets, any of the methods discussed work well.
For larger workloads:
- Prefer
string_to_table()for fixed delimiters. - Use
regexp_split_to_table()only when regular expressions are necessary. - Avoid repeatedly splitting the same values in complex queries.
- Normalize your database design whenever possible.
Regular expression processing is generally more computationally expensive than fixed delimiter splitting.
Common Use Cases
Splitting strings is useful for:
- CSV imports
- Legacy database migration
- Tag processing
- Product categories
- Permission lists
- Bulk data transformation
- ETL pipelines
- Reporting queries
Real-World Example
A company imports customer data from a legacy application where product categories are stored as a comma-separated string inside a single column. Before migrating the data into a normalized PostgreSQL schema, each category must become its own record so it can be linked through a junction table.
Using string_to_table() together with CROSS JOIN LATERAL, the migration script splits every category into individual rows while preserving the original customer ID. After the transformation, the data can be inserted into properly normalized tables, enabling efficient joins, indexing, and reporting.
This approach simplifies migration while avoiding manual parsing in application code.
Which Function Should You Choose?
| Function | Best Use Case |
|---|---|
string_to_table() | Fixed delimiter, fastest and simplest |
string_to_array() + unnest() | Array processing and additional array operations |
regexp_split_to_table() | Variable delimiters or pattern matching |
regexp_split_to_array() | Regular expression splitting into arrays |
For most applications with a known delimiter, string_to_table() is the preferred solution.
Best Practices Checklist
When splitting strings in PostgreSQL:
β
Use string_to_table() for fixed delimiters
β
Use regexp_split_to_table() only when necessary
β Trim whitespace after splitting
β Filter empty values
β
Preserve ordering with WITH ORDINALITY
β Keep original IDs during expansion
β Normalize data whenever possible
β Cast values to appropriate data types
β Test with malformed input
β Optimize repeated transformations
Common Mistakes to Avoid
Avoid:
β Storing multiple values in one column when designing new databases
β Using regular expressions for simple delimiters
β Forgetting to trim whitespace
β Ignoring empty elements
β Losing row relationships during splitting
β Leaving numeric IDs as text
β Repeating expensive split operations inside subqueries
Normalize Data Whenever Possible
String splitting is an excellent tool for imports, migrations, and legacy systems, but it should not replace proper database design. Whenever feasible, store related values in separate rows using normalized tables rather than packing multiple values into a single text column.
Using PostgreSQL's built-in functions makes the transition from denormalized data to a relational structure much easier.
Frequently Asked Questions (FAQ)
What is the easiest way to split a string into rows in PostgreSQL?
For a fixed delimiter such as a comma or semicolon, string_to_table() is generally the simplest and most efficient choice.
When should I use regexp_split_to_table()?
Use it when delimiters vary, when whitespace needs to be handled automatically, or when splitting based on more complex patterns defined with regular expressions.
What's the difference between string_to_table() and unnest(string_to_array())?
string_to_table() returns rows directly from a delimited string, while string_to_array() creates an array that can then be expanded with unnest(). The latter is useful if you need to perform additional array operations before converting the elements into rows.
Can I preserve the order of split values?
Yes. Add WITH ORDINALITY to include the position of each element in the output, making it easy to reconstruct or sort by the original order.
Wrapping Summary
PostgreSQL offers several powerful ways to split delimited strings into multiple rows, making it easier to work with imported data, legacy schemas, and text-based lists. Functions such as string_to_table(), string_to_array() with unnest(), and regexp_split_to_table() cover most real-world use cases, from simple comma-separated values to complex pattern-based parsing.
By selecting the appropriate function, preserving row relationships, handling whitespace and empty values correctly, and following normalization best practices, you can build SQL queries that are both efficient and easy to maintain.
π€ Share this article
Sign in to saveRelated Articles
Comments (0)
No comments yet. Be the first!