apply subscripting to text data type in PostgreSQL?

Question

Hope I read it correctly. Subscripting can be used in text type, like select name[1] will get the first character of the text column name? Now this will not work. Probably need a function? when I do it in psql.

ERROR: 42804: cannot subscript type text because it does not support subscripting
LOCATION: transformContainerSubscripts, parse_node.c:275

So, how to make subscripting available for text data type?

https://www.postgresql.org/docs/release/14.0/

Subscripting can now be applied to any data type for which it is a useful notation, not only arrays. In this release, the jsonb and hstore types have gained subscripting operators.

The release notes mention three types: arrays, hstore and jsonb - text is neither of them. So, no, you did not read it correctly. — user330315
– user330315, Commented Mar 8, 2022 at 15:14
for subscripting text data you can use the function substring() see the manual — Edouard
– Edouard, Commented Mar 8, 2022 at 15:23

Super Kai - Kazuya Ito · Accepted Answer · 2024-02-02 00:11:14Z

1

I got the same error below:

ERROR: cannot subscript type text because it does not support subscripting

When I tried to get the 2nd character b as shown below:

SELECT ('abc'::TEXT)[2]; -- Error

So, I created an array with these ways below, then I could get the 2nd character b without error. *My answer explains how to declare and use an array in detail:

SELECT (ARRAY['a','b','c']::TEXT[])[2]; -- b

SELECT ('{a,b,c}'::TEXT[])[2]; -- b

SELECT (string_to_array('a,b,c', ',')::TEXT[])[2]; -- b

answered Feb 2, 2024 at 0:11

Super Kai - Kazuya Ito

42.8k23 gold badges258 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

apply subscripting to text data type in PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related