Replace Infinity values in a PostgreSQL numeric[] array field

Question

I have a table in a PG 14 database having a column containing Infinity values in numeric[] arrays as follow:

SELECT id, factors_list FROM mytable ORDER BY id ASC LIMIT 2;

id | factors_list
---+-------------
1  | {Infinity,1,2.91825,2.2911174796669,1.58367915763394,1.96345397169765,1.41599564744287}
2  | {Infinity,1,1.0625,2.114,4.25,2.18021276595745}

The data type of this column is ARRAY (numeric[]) and the length of the array is variable (with some records being NULL):

SELECT column_name, data_type FROM information_schema.columns WHERE 
table_name = 'mytable' AND column_name = 'factors_list';


   column_name  | data_type 
----------------+-----------
 factors_list   | ARRAY

In order to restore this database table into an older (<14) PG database, I need to replace all Infinity values by any valid number, let's say 99999999.

How could I achieve that in an efficient way? (I have roughly 200'000 rows)

Why would you need to replace the Infinity values? Support for Infinity goes back until Postgres 8.2 (and possibly even earlier). What is this "older PG database" that doesn't support Infinity? — user330315
– user330315, Commented Jan 18, 2022 at 7:44
"PostgreSQL 13.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit" : ERROR: cannot convert infinity to numeric SQL state: 0A000 — swiss_knight
– swiss_knight, Commented Jan 19, 2022 at 22:20
Indeed numeric doesn't support infinity - but your column must be defined as float[] or double precision[] - you didn't mention that you want to convert this to numeric — user330315
– user330315, Commented Jan 19, 2022 at 22:24

swiss_knight · Accepted Answer · 2022-01-17 23:30:31Z

2

This simple update statement should do the job:

UPDATE mytable 
   SET factors_list = array_replace(factors_list, 'Infinity', 99999999)
 WHERE TRUE;

answered Jan 17, 2022 at 23:30

swiss_knight

8,38115 gold badges66 silver badges120 bronze badges

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

3 Comments

bfris Over a year ago

recommend to first copy some rows into a test table and update those, SELECT * INTO test_table FROM columns LIMIT 1000. UPDATEs always make me nervous.

sticky bit Over a year ago

@bfris: Then using transactions and no auto commit may help you... ;)

swiss_knight Over a year ago

It's definitely not recommended to be nervous with databases.

Collectives™ on Stack Overflow

Replace Infinity values in a PostgreSQL numeric[] array field

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related