Can I change the name of the collection?

[ryosek]

Hello,

Is it possible to change the name of the collection?
I want to change the Collection connected to an existing application. I would like to switch to a new Collection by creating a new Collection and then renaming that Collection to the name I am using now.

Thanks,

[perry]

Hi @ryosek , unfortunately it is not possible to rename a collection once it has been created. You could do it in a few steps, but maybe it would be helpful to understand a bit more about what you’re trying to achieve?

[ryosek]

Hello @perry, thank you for answering!
I have an application that allows users to browse the data in the “product._default.record” collection.
This time, I want to change the user_id stored in “product._default.record” from a string to a number.

Therefore, I decided to create a new collection “product._default.new_record” and test that the user_id is successfully changed to a number.
Then, by renaming “product._default.record” to “product._default.old_record” and “product._default.new_record” to “product._default.record”, the app can connect to the new_record collection.

[mreiche]

I want to change the user_id stored in “product._default.record” from a string to a number.

You could just update it in place.

update default:product._default.record set user_id=tonumber(user_id) where regexp_matches(user_id, ‘[0-9]{1,}’)

Or you could upsert from your new_record collection using insert-select UPSERT | Couchbase Docs

However, your application might be unhappy that the user_id is a number instead of a string.

[vsr1]

This can give ids not able to convert to number

SELECT META(c).id 
FROM mycollection AS c
WHERE tonumber(user_id) IS NULL;

you can store both in same collection and while doing predicate match TO_STR() on both sides
If string NO-OP, number converts to string. (Same as CAST)

WHERE TO_STR(user_id) = TO_STR($user_id)
Also change index.

OR

INSERT INTO old_record( KEY k , VALUE v)
SELECT META(v).id  AS k,  v
FROM record AS v
WHERE tonumber(user_id) IS NULL;

DELETE FROM record WHERE tonumber(user_id) IS NULL;

UPDATE record AS r 
SET user_id = to_number(user_id)
WHERE tonumber(user_id) IS NOT NULL;

EE, checkout eventing also Examples: Using the Eventing Service | Couchbase Docs

[ryosek]

@mreiche
Thank you for your reply!
I want to try other table operations on the copied table, so I want to keep a copy.

However, your application might be unhappy that the user_id is a number instead of a string

Why do you think this is unfortunate? The user_id is an auto-incrementing number created by another system, but is currently being imported as a string by mistake, so I am going to change it to a number.

@vsr1
Thank you for your reply!
I could copy table with the query like below.

INSERT INTO product._default.old_record (KEY META(k).id, VALUE k)
SELECT _record AS k
FROM product._default.record AS _record
WHERE tonumber(user_id) IS NULL;

I will read the docs as well. Thanks for sharing!

[vsr1]

@ryosek ,

In json number 10 and string “10” are different because value represents actual value and type of value
Say document has {“user_id”:10}
WHERE user_id = “10” will not match unless you do TOSTR(user_id) = “10”
I think what @mreiche meant means you might need to change application to reflect this (predicate/ projected values expected string and now changed number)

Correction to your query META(k).id in INSERT part not available , you need to project via SELECT

INSERT INTO product._default.old_record (KEY docid, VALUE k)
SELECT _record AS k, META().id AS docid
FROM product._default.record AS _record
WHERE tonumber(user_id) IS NULL;

[ryosek]

@vsr1
I see.
In terms of performance, usability, etc., is it better to keep every value as a string in Couchbase?
I thought it would be a bit redundant to put in to_number() every time I run a query like sort or between.

And thank you for correcting query. This query worked!

[vsr1]

normally user_id will be string. If you have number that is fine too.
Any language number is faster vs string due to storage , comparison

[ryosek]

@vsr1
Sorry for the confusion about the user ID.
And thanks for commenting on performance question!