Postgres Split String 1076

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial we will learn all about Postgres Split String in various scenarios, in order to fully understand the scope of this functionality. PostgreSQL’s Split_Part function will play a large part in splitting strings in Postgres into arrays. Along the way, we will learn how PostgreSQL arrays work, how PostgreSQL strings of text work, and how to convert one to the other using Postgres casting.

Prerequisites

  • PostgreSQL: A basic understanding of what Postgres is and how it works.
  • Variables: Knowledge of what non-array variables like decimal, numeric, integer, bigint, real, character, varchar, char, and text are in Postgres and how they work.

PostgreSQL Array

Arrays are groups of more than one item. An Array can save work and time, increase development ease, and more efficiently utilize resources such as processor, memory, and storage. In Postgres, an array looks like “1, 2, 3, 4, 5, 6” and “Jim, Ted, Sue, Mary, Tina”. We use “Items” to designate individuals in an array.

In this tutorial you will learn to use the Postgres Array. During this learning process we will provide both Python and Postgres array examples.

PostgreSQL Array Syntax

In the cross section between a database column and row you can have – instead of a decimal, integer, varchar, etc. – an array. Here’s an example of the syntax used for a Postgres array in SQL:

1
2
3
4
CREATE TABLE tbl_fruits (
    t_name_fruit text
    , arr_i_glucose INTEGER[]
)

Analysis

Examining the query above, the text data type used for the column called “t_name_fruit” may be familiar. The column we called “arr_i_glucose” is a PostgreSQL array data type. The brackets (“[]”) tell Postgres to set the column as an array type.

Let’s fill tbl_fruits with some test data to get a better idea of how Arrays work in PostgreSQL.

1
2
3
4
5
SELECT
    t_name_fruit text
    , arr_i_glucose INTEGER[]
FROM
    tbl_fruits

Returns

t_name_fruitarr_i_glucose
Blueberry30, 45
Strawberry48, 50, 50
Raspberry42, 41, 49
Lemon30, 36, 31, 34
Lime34, 35
Orange66
Grape64, 54, 63, 68

The above array results pulled from the arr_i_glucose column in tbl_fruits have “{” and “}” brackets that we removed to provide a more clear and easy to understand visual on the actual data returned.

Postgres array item

Now we’ll make use of one of the features of an array, which is to access any item in that array via an index. We’ll use “1” in this case to tell Postgres we want the first item in the array for any given row:

1
2
3
4
5
SELECT
    t_name_fruit text
    , arr_i_glucose INTEGER[1]
FROM
    tbl_fruits;

Results

t_name_fruitarr_i_glucose
Blueberry30
Strawberry48
Raspberry42
Lemon30
Lime34
Orange66
Grape64

Analysis

Notice in the results above that – unlike most languages – Postgres starts with one instead of zero for the first item in the array.

PostgreSQL Multidimensional arrays

The above array type is called a one dimensional array because for each “item” in that array, we stored only one set of values. If you want to store two sets of values, you call it a two dimensional array or multidimensional array.

PostgreSQL Multidimensional array example

1
2
3
4
5
SELECT
    t_name_fruit text
    , arr_i_glucose INTEGER[x][y]
FROM
    tbl_fruits

Here’s another example of how creating an array in Postgres would work:

1
2
DECLARE
    arr_names TEXT[] := ARRAY['Wins', 'Won', 'Will win'];

Postgres Split_Part

We’ll use the Split_part function from PostgreSQL to accomplish this.

First, let’s learn how to use PostgreSQL’s Split_part() via a study of its syntax and an example of its use.

Postgres Split_Part Syntax

1
SPLIT_PART(array OR string, delimiter, POSITION)

Analysis

  • array or string: We feed an array or string of delineated characters into the function as the first parameter.
  • delimiter: The second parameter is whatever character is used to delineate, such as a comma or dash symbol.
  • position: Which discrete item in that array or string to return.

Postgres Split_Part Example

1
SELECT SPLIT_PART("Blueberry, Strawberry, Raspberry", ",", 2);

The above use of split_part returns “Strawberry” because it is the second item in the array of items.

A way to manage data that may or may not appear like an array is to start with a string or convert data to a string and then use the Split_part function to get the parts of that string (text) that we want. Date manipulation is a common use for this function.

For our little project, look at the data below in tbl_trees:

ID_treed_picked
1002019-01-03
1022019-01-05
1042019-01-07
1062019-01-09
1082019-01-11

Our goal is to separate year, month, and day into separate columns. Split_Part is perfect for this operation. This is where we will also learn to use Postgres casting.

1
2
3
4
5
6
7
8
SELECT
    ID_tree
    , d_picked
    , split_part(d_picked::TEXT, "-", 1)::INTEGER AS i_year
    , split_part(d_picked::TEXT, "-", 2)::INTEGER AS i_month
    , split_part(d_picked::TEXT, "-", 3)::INTEGER AS i_day
FROM
    tbl_trees;

Results

ID_treed_pickedi_yeari_monthi_day
1002019-01-03201913
1022019-01-05201915
1042019-01-07201917
1062019-01-09201919
1082019-01-112019111

Analysis

We’ll look specifically at the first row, where ID_tree is 100. If you look at the value in the d_picked column, you see “2019-01-03”. Notice the delineator here is a dash. So, to retrieve the year, we know it is always the first item in that value. This is where we use the Postgres split_part function. This function requires a string to work on and we have a date type. So that is where we do “casting”, which is the “::TEXT” you see, which converts the entire d_picked column into text. THEN the next parameter is a dash so PostgreSQL knows what kind of delineator is being used. And for the final parameter, position, we put a 1 to say, “Get the part of this string that stops just before the first dash.” Finally, we cast the result as an integer so that we can place it in the i_year column that we want to type as an integer. Next we go through the exact same process for month and day.

Conclusion

In this tutorial we learned how to use the Postgres Split String function. PostgreSQL’s Split_Part function plays an important role in splitting arrays or strings with delineation in Postgres into arrays. We also learned how PostgreSQL arrays work, how PostgreSQL strings of text work in general, and how to convert data types using cast (casting). Code examples were provided.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.