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, Feb 24 '14 at 19:33
@ErwinBrandstetter Table definition added; pretty much what was described (a JSON column), and sample data. — darksky, Feb 24 '14 at 19:47

Erwin Brandstetter · Accepted Answer · 2021-09-06T02:18:04.043

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}

I still get `ERROR: default for column "namespace_ids" cannot be cast automatically to type integer[]`. Any ideas why? — darksky, Feb 24 '14 at 20:20
@Nayefc: Goes to show the importance of the table definition. I added a bit to my answer. — Erwin Brandstetter, Feb 24 '14 at 20:34
This works, thanks! I guess '[]'::json cannot be cast into an array using this way? Any reason why? — darksky, Feb 24 '14 at 21:02
@Nayefc: It can be cast just the same way, but the default clause is part of the *table definition*, which is another matter altogether. — Erwin Brandstetter, Feb 24 '14 at 21:04

Altering JSON column to INTEGER[] ARRAY

1 Answers1

column `DEFAULT`

Linked

Altering JSON column to INTEGER[] ARRAY

1 Answers1

column DEFAULT

Linked

column `DEFAULT`