SQL query formatting across SELECT vs UPDATE requires different quoting

[Steve_Replo]

Why does this seem to work:

UPDATE core_store SET referrer_key = NULL WHERE id = {{ inspector.selectedID }}

and this works:

SELECT _encrypted_shopify_access_token FROM core_store  WHERE id = '{{ inspector.selectedID }}'

but when i try to do either WHERE clause with a different quoting setup, it errors. My uuid is:

>> inspector.selectedID
"3f52e0e0-74dd-49a0-bfb7-ff035f4ba079"

So there seems to be a different handling approach for PostgreSQL across UPDATE vs SELECT? Notice these two tables are the same. Any idea why?

[ScottR]

I haven't come across this issue but is the 3f52e0e0-74dd-49a0-bfb7-ff035f4ba079 value being passed in with the actual quotation marks?

[Steve_Replo]

It's hard to tell exactly how it's being passed bc the quotes always show up in the green preview button but that's because I'm assuming it's a string.... here are two screenshots to highlight the confusion. These work but when i change the way the single quotes are added around the JS evaluation, it errors

[ScottR]

Two things:
If you remove the single quotes, does the SELECT statement work?
Also, is the DB column set as a number or something else, text?

is it possible for you to export some test data from your DB so I can try to reproduce?

[Steve_Replo]

Hopefully these help! Basically I get an error in both of these cases (error shown)

The core_store.id is a uuid
Probably no to give you some data for now

[bobthebear]

what's the column type set as in the database? it looks like the type is UUID? if so, I think the comparison ends up being varchar(36) and string, which will result in an error.

maybe try:
SELECT _encrypted_shopfiy_access_token FROM core_store WHERE id::text = {{ inspector.selectedID }}
or
WHERE id = {{ inspector.selectedID }}::uuid

an alternative would be to change the column type to 'text', but depending on your project/code this could break other things.

[ScottR]

@bobthebear Yep that's what I was trying to tease out... so good work....
@Steve_Replo I support bobthebear's suggestion.

[bobthebear]

@bobthebear

ahhh my bad!! I totally wouldn't have just given the answer straight up if I had noticed and would have offered some extra hints instead. I'll have to read more closely next time

ahhh my bad!! I totally wouldn't have just given the answer straight up if I had noticed and would have offered some extra hints instead. I'll have to read more closely next time

and i'm failing at clicking the correct reply button. i'm all thumbs today lol

[ScottR]

All good - glad you answered it...

[Steve_Replo]

Hmm on the SELECT where this works:

SELECT _encrypted_shopify_access_token 
FROM core_store 
WHERE id = '{{ inspector.selectedID }}'

Hmmm both:

WHERE id::text = {{ inspector.selectedID }}
AND
`WHERE id = {{ inspector.selectedID }}::uuid'

Both give the same syntax error at or near "b6" error (see previous screenshot for the specific selectedID i'm using here)

Honestly I feel like at this point it might just be something wrong with the way inspector is pulling in selectedID

[bobthebear]

i may have put the type conversion in the wrong place
WHERE id = {{ inspector.selectedID::uuid }}

this def seems like a type problem though. comparing a string to the postgresql UUID type is the only problem I can see, hopefully more qualified postgresql people will pop in here and correct me . I don't use the sql stuff often so you may need to play around with where to put ::uuid
like:
WHERE id = '{{ inspector.selectedID }}'::uuid
or specifying the type for both l-value and r-value:
WHERE id::uuid = {{ inspector.selectedID }}::uuid
WHERE id::uuid = '{{ inspector.selectedID }}'::uuid
WHERE id::uuid = {{ inspector.selectedId::uuid }}
WHERE id::text = {{ inspector.selectedID }}::text
WHERE id::text = '{{ inspector.selectedID }}'::text

have you tried changing the column type in the database to text?

the error message makes me think something doesn't like the dash/hypens so you might try these with inspector.selectedID.replace("-",""). i think i remember the postgresql docs saying it supports uuids both with and without the dash, but the problem might be on the javascript side where the dash is causing problems.