What is the appropriate way to update an array?

[i-h8-github]

I can define default arrays, insert arrays, and retrieve arrays. but I can't figure out how to update an array.

Thank you.

[jeremyevans]

Assuming you are using the pg_array_ops extension, you would call the appropriate method: https://sequel.jeremyevans.net/rdoc-plugins/classes/Sequel/Postgres/ArrayOp.html

It would probably be helpful to post the code you have and explain exactly what you want.

[i-h8-github]

Thank you very much, that seems to be working.

Where could I have looked to learn that syntax?

How would I replace an array entirely? Is there a more ergonomic means of achieving array-like functionality?

[jeremyevans]

The use of push is documented in the pg_array_ops extension: https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_array_ops_rb.html . Looks like remove and replace should also be documented there.

To replace an array (ignoring any existing value), you would assign a new array as the value of the column. Review the pg_array extension documentation for how to do that: https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_array_rb.html

[i-h8-github]

The methods described at https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_array_ops_rb.html are very clear and easy to understand. What I didn't understand was the fact that you have to pass a symbol to pg_array_op() in order to yield an object upon which calling those methods would yield valid syntax.

The very first thing I tried was replacing the entire array, following the insert example at https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_array_rb.html since there is no update example, I had successfully inserted arrays previously, and since other update examples I could find seemed to follow the same syntax as insert.

i.e.

favorites = [1]
account.update(favorites: Sequel.pg_array(favorites))

yields:

Sequel::DatabaseError: PG::InvalidTextRepresentation: ERROR: malformed array literal: "[1]" DETAIL: Missing "]" after array dimensions. CONTEXT: unnamed portal parameter $1 = '...

Actually, my very first approach was pulling the existing array out of the DB, manipulating that, and saving it back to the DB.

i.e.

favorites = account.first[:favorites]
favorites.push 1
account.update(favorites: favorites)

But it yields the same error.

[jeremyevans]

In situations like this, please create a post a self-contained example, like this:

begin
  DB.extension(:pg_array)
  DB.create_table?(:array_test) do
    column :t, 'int4[]'
  end
  ds = DB[:array_test]
  ds.insert(:t=>Sequel.pg_array([1]))
  p ds.all
  ds.update(:t=>Sequel.pg_array([2]))
  p ds.all
  array = ds.get(:t)
  array.push 3
  ds.update(:t=>array)
  p ds.all
ensure
  DB.drop_table(:array_test)
end

Running this in the console:

$ ruby bin/sequel -E postgres:///?user=sequel_test t.rb 
I, [2023-12-31T09:12:57.596569 #39507]  INFO -- : (0.000334s) SET standard_conforming_strings = ON
I, [2023-12-31T09:12:57.596855 #39507]  INFO -- : (0.000122s) SET client_min_messages = 'WARNING'
I, [2023-12-31T09:12:57.596957 #39507]  INFO -- : (0.000063s) SET DateStyle = 'ISO'
I, [2023-12-31T09:12:57.600737 #39507]  INFO -- : (0.000773s) SELECT CAST(current_setting('server_version_num') AS integer) AS v
I, [2023-12-31T09:12:57.604419 #39507]  INFO -- : (0.003185s) CREATE TABLE IF NOT EXISTS "array_test" ("t" int4[])
I, [2023-12-31T09:12:57.607310 #39507]  INFO -- : (0.001977s) SELECT "pg_attribute"."attname" AS "pk" FROM "pg_class", "pg_attribute", "pg_index", "pg_namespace" WHERE (("pg_class"."oid" = "pg_attribute"."attrelid") AND ("pg_class"."relnamespace" = "pg_namespace"."oid") AND ("pg_class"."oid" = "pg_index"."indrelid") AND ("pg_index"."indkey"[0] = "pg_attribute"."attnum") AND ("pg_index"."indisprimary" = 't') AND ("pg_class"."oid" = CAST(CAST('"array_test"' AS regclass) AS oid))) LIMIT 1
I, [2023-12-31T09:12:57.607935 #39507]  INFO -- : (0.000393s) INSERT INTO "array_test" ("t") VALUES (ARRAY[1]) RETURNING NULL
I, [2023-12-31T09:12:57.608146 #39507]  INFO -- : (0.000109s) SELECT * FROM "array_test"
[{:t=>[1]}]
I, [2023-12-31T09:12:57.608697 #39507]  INFO -- : (0.000298s) UPDATE "array_test" SET "t" = ARRAY[2]
I, [2023-12-31T09:12:57.608869 #39507]  INFO -- : (0.000091s) SELECT * FROM "array_test"
[{:t=>[2]}]
I, [2023-12-31T09:12:57.609203 #39507]  INFO -- : (0.000151s) SELECT "t" FROM "array_test" LIMIT 1
I, [2023-12-31T09:12:57.609563 #39507]  INFO -- : (0.000213s) UPDATE "array_test" SET "t" = ARRAY[2,3]::integer[]
I, [2023-12-31T09:12:57.609717 #39507]  INFO -- : (0.000103s) SELECT * FROM "array_test"
[{:t=>[2, 3]}]
I, [2023-12-31T09:12:57.611229 #39507]  INFO -- : (0.001414s) DROP TABLE "array_test"

Based on your error message (which uses bound variables), I also tested this and variations of it with the pg_auto_parameterize extension, and I was unable to reproduce the error you are getting. If you could please update the example above or post a new self contained example similar to it that shows the error you are experiencing, that would be helpful.

[i-h8-github]

Thank you. I had Sequel.extension :pg_array instead of DB.extension :pg_array, which caused the issue. I didn't grasp the distinction before, but looking at the docs again I can see I skimmed over that crucial detail.

By the way, the RDoc link for "Extensions/Plugins" at http://sequel.jeremyevans.net/documentation.html points to http://sequel.jeremyevans.net/rdoc-adapters/index.html

Thank you always for your help and patience. A happy and prosperous new year to you and yours.