Using a Rails Update to Add to a Text Column in Postgresql

Question

Thanks in advance for your help in this matter.

I have a model in rails that includes a postgresql column.

I want to add data (i.e. mycolumn = mycolumn || newdata) to an existing column. The Sql I want to create will look like this:

update MyOjbs set mycolumn = mycolumn || newdata where id = 12;

I would rather not select the data, update the attribute, and then write the new data to the database. A text column can grow relatively large, and I would prefer not to read this data if I don't need it.

I do not want to do this:

@myinstvar = MyObj.select(:mycolumn).find(12)
newdata = @myinstvar.mycolumn.to_s + newdata
@myinstvar.update_attribute(:mycolumn, newdata)

Do I need to execute an raw sql transaction to complete this?

Answer 1

, , , arel, rails.

, :

column_id = 12
newdata = "a custom string"

:

# Initialize the Table and UpdateManager objects
table = MyOjbs.arel_table
update_manager = Arel::UpdateManager.new Arel::Table.engine
update_manager.table(table)

# Compose the concat() function
concat = Arel::Nodes::NamedFunction.new 'concat', [table[:mycolumn], new_data]
concat_sql = Arel::Nodes::SqlLiteral.new concat.to_sql

# Set up the update manager
update_manager.set(
  [[table[:mycolumn], concat_sql]]
).where(
  table[:id].eq(column_id)
)

# Execute the update
ActiveRecord::Base.connection.execute update_manager.to_sql

SQL, :

UPDATE "MyObjs" SET "mycolumn" = concat("MyObjs"."mycolumn", 'a custom string') WHERE "MyObjs"."id" = 12"