Programming PostgreSQL

Splitting a Delimited String into Multiple Rows in PostgreSQL

November 21, 2026 5 min read

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() and regexp_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:

idtag
1Laptop
1Electronics
1Portable
2Book
2Education
3Phone
3Gadget
3Android

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:

ValuePosition
A1
B2
C3
D4

This is useful for ranked lists or ordered imports.


Splitting Multiple Columns

Suppose a table contains:

idcategories
1SQL,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?

FunctionBest 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 save

Comments (0)

No comments yet. Be the first!

Leave a Comment

Sign in to comment with your profile.

πŸ“¬ Weekly Newsletter

Stay ahead of the curve

Get the best programming tutorials, data analytics tips, and tool reviews delivered to your inbox every week.

No spam. Unsubscribe anytime.