3 ms·
> you need to do an adhoc query to look something up so you have to type in the key in a where clause Having a surrogate key here doesn’t help. A WHERE predica
by sgarland 2y ago
> you need to do an adhoc query to look something up so you have to type in the key in a where clause
Having a surrogate key here doesn’t help. A WHERE predicate can be rewritten as an INNER JOIN quite easily. Or you could use a subquery, or a CTE. Many options.
The other problems discussed are an engineering culture problem. Either you value correctness and consistency or you don’t.
- parpfish 2y agonot sure how converting a WHERE to a JOIN helps when you're doing adhoc queries like this to look up records: `SELECT * FROM users WHERE email LIKE 'FirstName@domain.net'`
- sgarland 2y agoI misunderstood your point; I thought you were saying having a surrogate key here would be better. For the above, either ensure that all email addresses are stored in one case (and index / query on the same), or if that’s impossible for some reason, you can query with ILIKE, optionally creating a trigram index (for Postgres; this isn’t a problem in MySQL since by default it’s entirely case-insensitive) on the column. Another option is to index with a functional index (CREATE INDEX user_email_lowercase ON user (LOWER(email))), and then code the API doing these lookups to cast to lowercase. That way, it’ll be retrieved and displayed however the user entered it, but retrieved based only on the CI version.