Postgresql: comparing arrays results in "malformed array literal" error

Question

I have a Rails app and a column that contains an array like ["manager", "engineer"] etc. and a where statement like this:

where("? = ANY roles", query)

which works find if I pass a single value for query. I want to be able to pass multiple values. I did some Googling and an simple solution was found:

where("? && roles", query )

except that if I pass something in like "['admin', 'guest']" I get this error:

PG::InvalidTextRepresentation: ERROR:  malformed array literal: "['admin', 'guest']"
LINE 1: ....                                       $1 AND ('[''admin'...
                                                             ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.

I suspect that there is some weird quote escaping issue but I can't figure it out. Those error messages results in a bunch of JSON Q&As but nothing that jumps out with a solution.

UPDATE

I always seem to find a clue after posting a question - I tried:

where("'{guest, admin}'::text[] && roles", query )

and it works - I still don't really know why but it does. Now I can't see how to get the ? back in there now so I can search on it.

UPDATE 2

I took the first answer below and did a bit of refactoring to get what I think is a simple elegant solution:

where("'{#{roles}}'::text[] && roles")

This way I can pass a simple text string to my application helper that this where clause sits. It handles single and multiple queries.

Would you be able to show what you have stored in the variable roles in update 2? — tnaught
– tnaught, Commented Dec 5, 2017 at 20:07

mu is too short · Accepted Answer · 2016-01-12 06:22:27Z

4

You're almost always better off using the array constructor syntax for arrays. From the fine manual:

4.2.12. Array Constructors

An array constructor is an expression that builds an array value using values for its member elements. A simple array constructor consists of the key word ARRAY, a left square bracket [, a list of expressions (separated by commas) for the array element values, and finally a right square bracket ]. For example:
SELECT ARRAY[1,2,3+4];
  array
---------
 {1,2,7}
(1 row)

ActiveRecord will expand an array value for a placeholder into a comma delimited list and that's exactly what the array[...] syntax wants between the brackets. So you'd say:

where('array[?] && roles', query)

This even does the right thing if query is a single value.

As far as your UPDATE goes, this:

'{guest, admin}'::text[]

is a string literal ('{guest, admin}') followed by a type cast (::) to an array-of-text (text[]). The '{...}' syntax inside the string is another form of an array that is easy to read but a hassle to properly build; the fine manual also covers this form:

8.15.2. Array Value Input

To write an array value as a literal constant, enclose the element values within curly braces and separate them by commas.

I use the array[...] version exclusively because it is easier to work with and more explicit as to what type the array elements are.

answered Jan 12, 2016 at 6:22

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mu is too short Over a year ago

Please don't use string interpolation to build SQL. roles could contain all sorts of unpleasant things that can break your "elegant" solution.

Dan Tappin Over a year ago

I get that but I have to manual the input in my views and you can't manipulate it via the params etc. Any suggestion on a better way to do this?

mu is too short Over a year ago

I don't understand this: "I have to manual the input in my views and you can't manipulate it via the params". Why does it have to be a comma-delimited string and why can't you manipulate it?

Dan Tappin Over a year ago

I will expand my question when I get back to my computer. What I am trying to say is that the 'roles' being feed into this search is not editable etc from the end user. It's a hard coded call in my view i.e. myhelper_function("manager, admin"). The risk is low that I break something but after seeing the comments I agree that should still try to sanitize my input and look to catch the double quotes etc.

mu is too short Over a year ago

You could say myhelper_function(%w[manager admin]), myhelper_function('manager', 'admin'), or myhelper_function(*%w[manager admin]) instead. Taking an unnecessary shortcut is false-laziness.

Wizard of Ogz · Accepted Answer · 2016-01-12 12:09:07Z

0

Edit

DO NOT do what this answer suggests since it is dangerous and fragile. It was one of those things I really did not think through and regret posting in public

You want your array literal to be formatted like '{"admin", "guest"}'

Response to edited OP

query_terms = ["admin", "guest"]
query = "{#{ query_terms.map {|term| %Q("#{ term }") }.join(",") }}"
where("? && roles",  query)

edited Jan 12, 2016 at 12:09

answered Jan 12, 2016 at 5:29

Wizard of Ogz

12.6k2 gold badges44 silver badges44 bronze badges

2 Comments

mu is too short Over a year ago

What happens if one of the query_terms contains a double quote?

Wizard of Ogz Over a year ago

@muistooshort Then I hope you sanitized your input! Answer removed since it was horrible and should not be used.

Collectives™ on Stack Overflow

Postgresql: comparing arrays results in "malformed array literal" error

2 Answers 2

5 Comments

Edit

Response to edited OP

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Edit

Response to edited OP

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related