-
-
Notifications
You must be signed in to change notification settings - Fork 0
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Suggest type conversions for TEXT columns #13
Comments
Here's a query that can do that: select
'contains_non_integer' as col
from
sortable
where
cast(cast(sortable AS INTEGER) AS TEXT) != sortable
limit
1 This will return 0 results if every column is an integer - it will shortcut the query and return a result as soon as it finds a non-integer column. Example for a column containing only integers: https://latest.datasette.io/fixtures?sql=select%0D%0A++%27contains_non_integer%27+as+col%0D%0Afrom%0D%0A++sortable%0D%0Awhere%0D%0A++cast%28cast%28sortable+AS+INTEGER%29+AS+TEXT%29+%21%3D+sortable%0D%0Alimit%0D%0A++1 And for a column that instead contains floating point: https://latest.datasette.io/fixtures?sql=select%0D%0A++%27contains_non_integer%27+as+col%2C+*%0D%0Afrom%0D%0A++sortable%0D%0Awhere%0D%0A++cast%28cast%28sortable_with_nulls+AS+INTEGER%29+AS+TEXT%29+%21%3D+sortable_with_nulls%0D%0Alimit%0D%0A++1 |
Maybe |
Idea: return the first detected value that is not an integer. Then code can see if that value is not-a-float - if it's obviously not-a-float we don't have to run the float detection on it. |
Running this query against my dogsheep-beta index is a good test, because the select
'contains_non_integer' as col, *
from
search_index
where
cast(cast(key AS INTEGER) AS TEXT) != key
limit
1 |
This method works for detecting integers but doesn't work for floats - because |
Useful thread on StackOverflow: https://stackoverflow.com/questions/32528759/how-to-check-if-a-value-is-a-number-in-sqlite |
This would be really useful, especially as a prompt that displays at the top of the table page (via a Every time I demonstrate CSV import to someone I have to give them a little spiel about how important it is to ensure the types are correct - this would be a good way to improve that. |
In simonw/sqlite-utils#282 I added the So there's no |
Could I use a custom |
I'm not going to do this. The new example display from here solves the same problem: |
It would be great to suggest things like "the category column is all numbers but is currently TEXT, you could convert it to INTEGER"
The text was updated successfully, but these errors were encountered: