how to update array in crate

Question

I would like to know how I can update a value stored in an array, in crate.io

I have a blog table - blog_tbl
A column, with data type array - tags
A id column
Inside the tags column I have - ["tag1","tag2","tag3"]

I would to know how I would go about changing 'tag1' to 'tag99'

I tried

update blog_tbl set tags['tag1'] = 'tag99' where id = '1';

Also how would I add one the the end? so making it -

["tag1","tag2","tag3","tag4"]

many thanks

score 1 · Answer 1 · answered Aug 07 '15 at 13:39

1

Unfortunately it's not possible currently. Array elements can only be selected using the subscript notation (e.g. select tags[1] from blog_tbl;) but not updated. Maybe add a GH issue requesting that feature.

answered Aug 07 '15 at 13:39

Sebastian Utz

719
3
9

score 0 · Answer 2 · answered Jan 17 '17 at 15:21

You can use the pattern found here: https://crate.io/docs/reference/sql/occ.html#optimistic-update

However, that requires you to perform the modification on client side. Pseudo code:

updated = False
while not updated:
    cursor.execute('SELECT array_field, "_version" FROM table WHERE id=1')
    row = cursor.fetchone()
    current_array_field = row[array_field]
    current_array_field.append('newtag')
    cursor.execute('UPDATE array_field = current_array_field WHERE id=1 AND "_version" = row[version]')
    if cursor.rowcount > 0:
        updated = True

This will make your update semi safe for concurrent updates of the same field.

how to update array in crate

2 Answers2