CODEWITHSUNDEEP
sqlpostgresql
user419017
user419017

Reputation:

Postgres: Bulk update query to update pointer for an array?

Currently have this in a table:

+----+--------+--------------+------------------+
| id |  site  | location_ids | current_location |
+----+--------+--------------+------------------+
|  1 | google | [1,2,3]      |                2 |
|  2 | yahoo  | [2,3]        |                3 |
|  3 | reddit | [1]          |                1 |
|  4 | stack  | [1,2,4]      |                2 |
+----+--------+--------------+------------------+

What I want to do is a bulk update query that will update the current location to either the next item in the array, or, if we've reached the end of the array, reset to the first item.

I am currently doing this manually and building the logic in my application:

update checks as chk set
  current_location = c.current_location
from (values
      (1, 3),
      (2, 2),
      (3, 1),
      (4, 4)
) as c(id, current_location)
where c.id = chk.id

Upvotes: 0

Views: 176

Answers (3)

Haleemur Ali
Haleemur Ali

Reputation: 28303

The case expression the other posters have used could be replaced with mod function for a slightly shorter solution:

% is the infix version of mod

UPDATE checks
SET current_location = location_ids[
      array_position(location_ids, current_location) % cardinality(location_ids) + 1
]

Upvotes: 4

user330315
user330315

Reputation:

I am assuming Postgres 9.5 or higher for this answer:

You can find the index of the current location ID by using array_position(location_ids, current_location). The next location ID should be the one at the next index, unless that index is bigger than the length of the array. That can be put into a single expression:

case 
  when 
   array_position(location_ids, current_location) + 1 > cardinality(location_ids) 
     then 1 
   else array_position(location_ids, current_location) + 1 
end

This expression can directly be used in an update statement:

update checks
   set current_location = 
        location_ids[case 
                      when array_position(location_ids, current_location) + 1 > cardinality(location_ids) 
                        then 1 
                      else array_position(location_ids, current_location) + 1 
                    end];

Upvotes: 2

404
404

Reputation: 8572

There may be a better way to write it that doesn't involve calling array_position twice, but if you're using PG 9.5 (for the array_position function) you could do:

UPDATE your_table
SET current_location = location_ids[CASE WHEN array_length(location_ids, 1) = array_position(location_ids, current_location) THEN 1 ELSE array_position(location_ids, current_location) + 1 END];

So it gets the index of current_location and checks if it's at the end of the array. If it is, it gets the value at index 1 (arrays indexes in PG are 1-based). If it's not, it gets the value at that position + 1.

Upvotes: 2

Related Questions