Question 216701 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Split word into letters

I would like to split a text data type and CAST it as a text[].
Something like this: ‘Hello world’ => [‘h’, ‘e’, ‘l’, ‘l’, ‘o’, ‘ ‘, ‘w’, ‘o’, ‘r’, ‘l’, ‘d’].

More specifically the table will look something like this

SELECT some_string,
  split_into_array(some_string) AS some_array
FROM table_name

I’m fairly new to PostgreSQL, but I’ve been reading the documentation.

I wasn’t able to find anything here
Nor was I able to find anything that seemed appropriate in the similar questions audit.

I tried this…

SELECT some_string,
    CAST(some_string AS text[]) AS res
FROM table_name;

But I got the error

ERROR:  malformed array literal: "I am a string"
DETAIL:  Array value must start with "{" or dimension information.
SQL state: 22P02

Thanks for your help.

Tags:

2

Answers


  1. 0

    What version of PostgreSQL? If it’s version 14 or later, then you can use string_to_array(some_string, NULL). For older versions, use regexp_split_to_array(some_string, '.{0}').

    Login or Signup to reply.
  2. 0

    As per my understanding the error occured because you attempted to cast the text data straight as a text[], however the issue happened because PostgreSQL thought the array literal should either begin with '{' or contain dimension information. Use the regexp_split_to_array method to convert a text data type into an array of characters, which creates the desired array format without the need for manual array literal formatting.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search