How to update a column of type array with postgresql and ruby on rails

Question

I have a column for a table that was created with following ruby on rails migration

def change    
    add_column :matches, :st_history, :smallint, array: true, default: []
end

on a postresql database. I wish to reset all values of the column back to default. I have tried

Match.update_all(st_history: [])

but this does not change any of the fields. Looking at the api documentation, it states that 'It should receive only values that can be passed as-is to the SQL database', so I suspect that the array is a complex datatype that will not work with a simple update_all command. The database has many millions of rows, so I do not want to update each row individually. What is a fast way of doing this?

EJ2015, you were right, I just forgot to reload. Do you want to put this as an answer, I will accept it. It is too late to delete the question as someone has put an answer. — Obromios
– Obromios, Commented Sep 25, 2017 at 6:12

EJAg · Accepted Answer · 2017-09-25 06:15:15Z

1

This actually works. Just need to reload.

answered Sep 25, 2017 at 6:15

EJAg

3,3382 gold badges16 silver badges23 bronze badges

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

2 Comments

Obromios Over a year ago

It turned out that although this works, but would take 80 hours to use on my database, so I ended up using Aniket's answer which only took a few seconds.

EJAg Over a year ago

Just curious, how big is your db? update_all sends one SQL command to db, skipping ActiveRecord completely. Why would it take that long?

Aniket Tiwari · Accepted Answer · 2017-09-26 05:37:12Z

1

You can try by creating a migration .And can give a default value

update_column :table_name, :col_name, :integer, array: true, null: false, default '{}'

edited Sep 26, 2017 at 5:37

answered Sep 25, 2017 at 5:58

Aniket Tiwari

4,0365 gold badges26 silver badges74 bronze badges

1 Comment

Obromios Over a year ago

Aniket, it should be 'change_column' not 'update_column'. For a large database this approach is magic. On my database it only took a few seconds instead of the 80 hours for my original approach.

Collectives™ on Stack Overflow

How to update a column of type array with postgresql and ruby on rails

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related