NOT IN [array]

[BeeRich33]

I have a statement that isn't working.  It's a SELECT statement that wants to select a single value from a field that avoids values in a provided Ruby array:

  AVOIDS = %w(alpha bravo charlie)

  tm = PG::TypeMapByColumn.new([PG::TextEncoder::Array.new(elements_type: PG::TextEncoder::String.new)])

  res = conn.exec_params( %Q{ SELECT fvalue FROM mytable 

    WHERE updated::date < (CURRENT_DATE - interval '3 days') 

      AND status = 'active'

      AND hashtag NOT IN ($1) 

    ORDER BY updated ASC

    LIMIT 1 }, AVOIDS, 0, tm).to_a

I want to use a Ruby array inside a SQL statement, but I have no clue how to format it, or create a TypeMap for this to work properly.  I tried to coerce a manufactured string for insertion, but that isn't working either.

How can I use the PG formatting methods to ready an array for use?

Any advice appreciated.  

Cheers

[BeeRich33]

found it:

 

AND status = 'active'

AND NOT hashtag = ANY ($1) 

[Lars Kanis]

Didn't we discuss this in https://bitbucket.org/ged/ruby-pg/issues/292/ruby-array-to-proper-usage-format ?

--
You received this message because you are subscribed to the Google Groups "ruby-pg" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ruby-pg+u...@googlegroups.com.
To post to this group, send email to rub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ruby-pg/48df44a2-ac8d-4571-80d5-269c0847aa5f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[BeeRich33]

A bit, yes.  But it came back.  

Second, I'm finding different syntax for my Postico client, and the discussion we had.  

AND NOT hashtag = ANY ($1) 

is what I found to work in this instance, whereas Postico needs...

AND hashtag NOT IN ('instagood', 'rosewood') 

It's hugely confusing, even if I'm pushing out odd solutions.  And I can't find anything else than our discussion to be anything relevant.  It seems there's a myriad of options, which only work in one environment.

On Friday, April 5, 2019 at 10:02:04 AM UTC-4, Lars Kanis wrote:

Didn't we discuss this in https://bitbucket.org/ged/ruby-pg/issues/292/ruby-array-to-proper-usage-format ?

On Fri, Apr 5, 2019, 14:34 BeeRich33 <bee...@gmail.com> wrote:

found it:

 

AND status = 'active'

AND NOT hashtag = ANY ($1) 

--
You received this message because you are subscribed to the Google Groups "ruby-pg" group.

To unsubscribe from this group and stop receiving emails from it, send an email to rub...@googlegroups.com.

[Chris Bandy]

"Array" is a type in PostgreSQL[1]. The Encoders provided by PG (gem) safely convert Ruby Arrays to PostgreSQL Arrays.

`hashtag = ANY ($1)` compares hashtag to the elements inside the first parameter of the statement[2]. That parameter needs to "be" PostgreSQL array.

The syntax for array literals uses braces and commas and is sensitive to whitespace.[3] Use the Encoders to stay sane.

You should be able to use this same syntax in Postico, something like:

  AND NOT hashtag = ANY ('{instagood,rosewood}')

When I (human) am typing out literals, however, I find the `ARRAY[]` constructor syntax is easier to get right:

  AND NOT hashtag = ANY (ARRAY['instagood', 'rosewood'])

[1]: https://www.postgresql.org/docs/current/arrays.html

[2]: https://www.postgresql.org/docs/current/functions-comparisons.html

[3]: https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT

-- Chris

[BeeRich33]

OK I got it to work.  I agree with you, re: writing out ARRAY by hand.

The documentation is a bit thin when it comes to formatting an array.  

tm = PG::TypeMapByColumn.new([

  PG::TextEncoder::String.new,

  PG::TextEncoder::Array.new(elements_type: PG::TextEncoder::String.new),

  PG::TextEncoder::Integer.new

  ])

If my array was of mixed types, I would only have one option with regards to any translation, as all the elements would become that one type.  

Kudos to Lars for bringing this typemap to my attention before.  

[Chris Bandy]

On Sunday, April 7, 2019 at 3:10:22 AM UTC-5, BeeRich33 wrote:

If my array was of mixed types, I would only have one option with regards to any translation, as all the elements would become that one type.  

 

The elements of a PostgreSQL Array must all be the same type. In PostgreSQL, there isn't a single type that is "array"; more like lots of types that work with array functions/behavior. For example, "array of floats" `float[]`, "array of integers" `int[]`, etc. There is no "array of find-out-later".

If you need/want to store information like that in a single column/value, take a look at `jsonb`. It is a single value/type, but you can go wild with its contents. It becomes your responsibility to know what's in there, e.g. which indexes of a JSON array have values that can participate in a `sum()`. 

-- Chris

[BeeRich33]

OK, none of this is column-dependent.  It's about qualifying using lists.  

IN {array}

NOT IN {array}

Trying to let the database do the work instead of multiple database calls.  Always leaning on PostgreSQL as much as I can.  That last post was out of curiosity, as in my case, there would only be one type in the array in use.  I've not used it but I like the concept of hstore.