Fork me on GitHub
#sql
<
2021-01-22
>
Malik Kennedy01:01:05

With conman I get

ERROR: function lower(jsonb) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. 
The query in question though seems (to me) to be clearly specify a text column (there are sibling jsonb columns). Is there a place I can learn about how to pass conman explicit type casts?

seancorfield02:01:28

@mksybr We'll need a lot more information to help you with that 🙂 How are you doing the query that causes that error?

seancorfield02:01:08

It looks like conman wraps HugSQL -- do you have your queries in separate .sql files that are being loaded by conman/HugSQL?

seancorfield02:01:30

If so, you can put whatever SQL you need -- including casts -- into the .sql file.

seancorfield02:01:32

Ugh, conman uses a global dynamic var for the connection 😞

Malik Kennedy14:01:48

Sorry for the delay. Yes I have the causitive query: Sometimes its get-user-by-username, othertimes its get-user-by-email

-- :name get-user-by-username
-- :command :query
-- :result :one
-- :doc Selects the user matching the username
SELECT
  id,
  status,
  email,
  username,
  password,
  user_data,
  history,
  permissions
  FROM
      users
 WHERE
  LOWER(username) = LOWER(:username);

-- :name get-user-by-email
-- :command :query
-- :result :one
-- :doc Selects the user matching the email
SELECT
  id,
  status,
  email,
  username,
  password,
  user_data,
  history,
  permissions
  FROM
      users
 WHERE
  LOWER(email) = LOWER(:email);
The confusing part is LOWER(jsonb) is what it complains about, but I didnt think email or username are being passed as jsonb. Re global dynamic var, yes that actually stumped me up a bit because I found a example that explicitly passed in the db connection. I only have one database right now, which seems like it might be a benefit of being more explicit, is there other benefits to avoiding the global dynamic approach?

lukasz14:01:32

The JSONB error usually happens when you pass a map or vector to the query function. I would sprinkle some assert here and there to verify that the email key is indeed a string

❤️ 7
lukasz14:01:49

(or spec, or Schema etc)