How to select a UUID from a prepared statement in Postgres?

Question

I'm trying to select a user based on a UUID:

const client = await this.pg.connect()
const { rowsAct } = await client.query(`SELECT * FROM users WHERE uid=$1`, [
  userUUID
])

I also tried without the variable:

const client = await this.pg.connect()
const { rowsAct } = await client.query(`SELECT * FROM users WHERE uid=$1`, [
  '4fcf0ca3-4e26-40a9-bbe5-78ff8fdb6e0f'
])

I tried using ::uuid casting but maybe I did it wrong. The returned rowsAct is always undefined.

I verified the userUUID variable was populated and was a valid uuid: console.log(typeof userUUID) // string

What am I doing wrong? How can I properly select a row form it's UUID?

Thanks!

Answer 1

You'll need to wrap your argument in parentheses before applying the ::uuid type cast so that the prepared statement can properly interpolate argument.

SELECT * FROM users WHERE uid = ($1)::uuid

You can also cast the column itself to text but it's less performant since the query will have to cast each row.

SELECT * FROM users WHERE uid::text = $1