Altering JSON column to INTEGER[] ARRAY

Question

I have a JSON column that contains an array of integers. I am trying to convert it to an INTEGER[] column, but I'm running into casting errors.

Here's my final alter version:

ALTER TABLE namespace_list ALTER COLUMN namespace_ids TYPE INTEGER[] USING string_to_array(namespace_ids::integer[], ',');

However, this throws this error: ERROR: cannot cast type json to integer[]

Any ideas how I can abouts this conversion? I've tried several things but I end up with the same error. Seems like going json --> string --> --> array does not work. What are my options?

Edit:

Table definition:

db => \d+ namespace_list;

Column         |   Type   |  Table "kiwi.namespace_list" Modifiers|
---------------+----------+--------------------------------------+
id             | integer  | not null default nextval('namespace_list_id_seq'::regclass)
namespace_ids  | json     | not null default '[]'::json

Sample data:

id | namespace_ids | 
-------------------+
1 | [1,2,3]        |

Providing a table definition and a few sample values would be the polite thing to do .. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 24, 2014 at 19:33
@ErwinBrandstetter Table definition added; pretty much what was described (a JSON column), and sample data. — darksky
– darksky, Commented Feb 24, 2014 at 19:47

Erwin Brandstetter · Accepted Answer · 2021-09-06 02:18:04Z

8

Assuming no invalid characters in your array.

IN Postgres 9.4 or later use a conversion function as outlined here:

How to turn JSON array into Postgres array?

CREATE OR REPLACE FUNCTION json_arr2int_arr(_js json)
  RETURNS int[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js)::int)';

ALTER TABLE namespace_list
  ALTER COLUMN namespace_ids DROP DEFAULT
, ALTER COLUMN namespace_ids TYPE int[] USING json_arr2int_arr(namespace_ids);

db<>fiddle here

For Postgres 9.3 or older:

ALTER TABLE namespace_list
ALTER COLUMN namespace_ids TYPE INTEGER[]
      USING translate(namespace_ids::text, '[]','{}')::int[];

The specific difficulty is that you cannot have a subquery expression in the USING clause, so unnesting & re-aggregating is not an option:

SELECT ARRAY(SELECT(json_array_elements(json_col)::text::int))
FROM   namespace_list;

Therefore, I resort to string manipulation to produce a valid string constant for an integer array and cast it.

column `DEFAULT`

If there is a column default like DEFAULT '[]'::json in your actual table definition added later, drop it before you do the above. You can add a new DEFAULT afterwards if you need one. Best in the same transaction (or even command):

ALTER TABLE namespace_list
   ALTER COLUMN namespace_ids DROP DEFAULT
,  ALTER COLUMN namespace_ids TYPE INT[] USING translate(namespace_ids::text, '[]','{}')::int[]
,  ALTER COLUMN namespace_ids SET DEFAULT '{}';

db<>fiddle here
_{Old sqlfiddle}

edited Sep 6, 2021 at 2:18

answered Feb 24, 2014 at 19:52

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

4 Comments

darksky Over a year ago

I still get ERROR: default for column "namespace_ids" cannot be cast automatically to type integer[]. Any ideas why?

Erwin Brandstetter Over a year ago

@Nayefc: Goes to show the importance of the table definition. I added a bit to my answer.

darksky Over a year ago

This works, thanks! I guess '[]'::json cannot be cast into an array using this way? Any reason why?

Erwin Brandstetter Over a year ago

@Nayefc: It can be cast just the same way, but the default clause is part of the table definition, which is another matter altogether.

Collectives™ on Stack Overflow

Altering JSON column to INTEGER[] ARRAY

1 Answer 1

column `DEFAULT`

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

column DEFAULT

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

column `DEFAULT`