0

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
Improve this question
4
  • What data type is location_ids? Commented Apr 18, 2018 at 11:34
  • It's an array of ints. Commented Apr 18, 2018 at 11:35
  • What version of PG are you using? Commented Apr 18, 2018 at 11:39
  • 9.6, but the version is flexible as I'm working with a fresh db. Commented Apr 18, 2018 at 11:49

3 Answers 3

Reset to default
4

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
]
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Are there any additional performance implications with this?
probably minimal either way. here we call array_position, array_length & mod once each, the case based solution has a branch and calls array_length twice.
Yup. Fair enough. Thanks.
Clever, I like it!
2

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.

Improve this answer

1 Comment

Thanks, appreciate it.
2

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];
Improve this answer

1 Comment

Thanks! That'll do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.