consider uuid to varchar cast

[joefutrelle]

https://dba.stackexchange.com/questions/177733/postgresql-custom-operator-uuid-to-varchar

if this wouldn't mess up the Django ORM, it would be really convenient for writing raw sql queries. In the stack overflow question they're going the opposite way than we would -- we would make an implicit cast from uuid to varchar so we can compare timeseries IDs without having to cast the uuids to varchar.

this would be a custom sql migration and would probably not break the ORM, but I'm not going to try it on the live server!

[joefutrelle]

the alternative would be making a view of classify_timeseries that would include the cast, and use that for bulk queries, but that's a less elegant solution.

[LouisK130]

I'm not sure I fully understand. The issue is that the type of timeseries_id is uuid in classify_timeseries but varchar in others, like classify_classification, so comparisons require casts?

Is it just a question of whether or not you have to include ::uuid or ::varchar during comparisons in a query?

[joefutrelle]

Yes, the only purpose of the cast would be to make queries slightly less annoying to write.

[LouisK130]

Is it preferable to simply update classify_classification and classify_tag to have timeseries_id columns of type uuid? Shouldn't require too much effort. I honestly can't remember if I had a legitimate reason for making them text or if it was just ignorance.

On a related note, do you use the classify_winning_class view? I never noticed it until now, so I certainly haven't been using it in queries within the app. It would need to be regenerated if we change the schema.

[joefutrelle]

I'm loathe to change the schema of the django-controlled tables, especially right now when we have to deal with the at-sea database integration from EXPORTS.

wrt classify_winning_class view, that is in the 0003_summary_views migration, so it doesn't need to be regenerated if we make another migration.

Summary views are handy to avoid having to construct complex queries on the client side, when we have a few that we'll want to do routinely. Adding them as custom SQL migrations doesn't break the ORM, so we can add them willy-nilly, as far as I'm concerned.