/ ID: 1076 Title: Postgres Split String 1076 Meta Description: Learn about Postgres Split String. Code samples included. Meta Keywords: Postgres Split String Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: PostgreSQL, Split String, Cast Status: Published /
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.
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.
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:
SQL
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.
SQL
SELECT
t_name_fruit text
, arr_i_glucose integer[]
FROM
tbl_fruits
Returns
| t_name_fruit | arr_i_glucose | |--------------|---------------:| | Blueberry | 30, 45 | | Strawberry | 48, 50, 50 | | Raspberry | 42, 41, 49 | | Lemon | 30, 36, 31, 34 | | Lime | 34, 35 | | Orange | 66 | | Grape | 64, 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.
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:
SQL
SELECT
t_name_fruit text
, arr_i_glucose integer[1]
FROM
tbl_fruits;
Results
| t_name_fruit | arr_i_glucose | |--------------|--------------:| | Blueberry | 30 | | Strawberry | 48 | | Raspberry | 42 | | Lemon | 30 | | Lime | 34 | | Orange | 66 | | Grape | 64 |
Analysis
Notice in the results above that - unlike most languages - Postgres starts with one instead of zero for the first item in the array.
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.
SQL
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:
SQL
DECLARE
arr_names TEXT[] := ARRAY['Wins', 'Won', 'Will win'];
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.
sql
SPLIT_PART(array or string, delimiter, position)
Analysis
sql
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_tree | d_picked | |--------:|-----------:| | 100 | 2019-01-03 | | 102 | 2019-01-05 | | 104 | 2019-01-07 | | 106 | 2019-01-09 | | 108 | 2019-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.
sql
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_tree | d_picked | i_year | i_month | i_day | |--------:|-----------:|-------:|--------:|------:| | 100 | 2019-01-03 | 2019 | 1 | 3 | | 102 | 2019-01-05 | 2019 | 1 | 5 | | 104 | 2019-01-07 | 2019 | 1 | 7 | | 106 | 2019-01-09 | 2019 | 1 | 9 | | 108 | 2019-01-11 | 2019 | 1 | 11 |
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.
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.