Обсуждение: what am I doing wrong with this query?

select  array_to_string(conkey,',') from pg_constraint where contype =
'p' and conrelid = 17059

returns a value of 1,2,18 for the array to string function,

when I do this it does not return true:

select case when 18 in (array_to_string(conkey,',')) then true else
false end from pg_constraint where contype = 'p' and conrelid = 17059

but this one does return true

select case when 18 in (1,2,18) then true else false end from
pg_constraint where contype = 'p' and conrelid = 17059

How come the function does not work in the IN statement?  I tried
casting it to a varchar, but that did not work either.


Thanks,

Tony

Tony Caduto schrieb:
> select  array_to_string(conkey,',') from pg_constraint where contype =
> 'p' and conrelid = 17059
>
> returns a value of 1,2,18 for the array to string function,
>
> when I do this it does not return true:
>
> select case when 18 in (array_to_string(conkey,',')) then true else
> false end from pg_constraint where contype = 'p' and conrelid = 17059

When you try:

SELECT (array_to_string(conkey,',')); You will see
why. It basically produces:

('1,2,18') which isnt by far equivalent to (1,2,18)


> but this one does return true
>
> select case when 18 in (1,2,18) then true else false end from
> pg_constraint where contype = 'p' and conrelid = 17059
>
> How come the function does not work in the IN statement?  I tried
> casting it to a varchar, but that did not work either.

Its all horribly wrong ;)
1.) Try to avoid arrays in favour of real tables - queries are
    easy and fast

2.) if you cant avoid, try WHERE 18 ANY conkey; or the like.
    Look up the documentation for real syntax. Dont mix
    text/char/varchar with what you type.

HTH
Tino

Never mind, I figured it out, I had to use ANY instead of IN, works fine
now.

Thanks,

Tony