If you’re getting a PostgreSQL error that reads something like “cannot subscript type text because it does not support subscripting” when selecting data from a database, it’s probably because you’re trying to perform some sort of array operation against a non array value.

The above error specifically mentions text data but we could get the same error when using a different data type, like an integer, character varying, etc.

To fix this issue, be sure to run the array operations against actual arrays. If working with non array data, then don’t use array operations against that data.

Example of Error

Here’s an example of code that produces the error:

SELECT c1[3] FROM (SELECT '{32,50,28}' as c1);

Result:

ERROR:  cannot subscript type text because it does not support subscripting
LINE 1: SELECT c1[3] FROM (SELECT '{32,50,28}' as c1);
               ^

Here’s a database example:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1
(
    c1 text
);

INSERT INTO t1 VALUES 
    (
        '{ 1,2,3 }'
    );

SELECT c1[2] FROM t1;

Result:

ERROR:  cannot subscript type text because it does not support subscripting
LINE 1: SELECT c1[2] FROM t1;
               ^

In both cases I was trying to extract an array element from a text value (which is not an array). This resulted in the error occurring.

The text data type doesn’t support subscripting, which is when we put an index number within square brackets to retrieve data from an array.

Solution

We can fix the above issue by making sure the data is stored in an array. For the first example we could do this:

SELECT c1[3] FROM (SELECT '{32,50,28}'::text[] as c1);

Result:

28

Here I cast the text value to a text array by appending ::text[] to the text value.

For the second example, we could change the data type of the column:

ALTER TABLE t1 ALTER COLUMN c1 TYPE text[] USING (c1::text[]);

Result:

ALTER TABLE

In this example I explicitly converted the column by including the USING clause in the ALTER TABLE statement. In my case I needed to do that, because if I didn’t, I would get another error. So it’s a good idea to include the USING clause when converting the column.

Bear in mind that any data in the column will need to conform to the new data type. If it doesn’t, you might get another error. In this case you may need to check whether there’s a problem with the data or with your query.