Geek asks and answers

Changing JSON column for INTEGER [] ARRAY

I have a JSON column containing an array of integers. I am trying to convert it to an INTEGER [] column, but I encountered casting errors.

Here is my latest modified version:

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

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

Any ideas how I can do this? I tried a few things, but in the end I got the same error. It seems that json → string → -> array transition is not working. 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]        |
+3
source share
1 answer

This can do the job if you have no invalid characters in your array:

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

→ SQLfiddle

, USING, :

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

, .

column DEFAULT


, . default '[]'::json , .
: , , . .

ALTER TABLE namespace_list ALTER COLUMN namespace_ids DROP DEFAULT;

DEFAULT, . , .

+2

More articles:


All Articles