Parameterization of an array of enums?

Question

I have a table where one of the fields is an array of enums. For example let say this is what it looks like:

CREATE TYPE foobar AS ENUM (
  'FOO',
  'BAR'
);

CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  foobarray foobar[] DEFAULT ARRAY['FOO']::foobar[]
);

When I try to use node-postgres to insert/update a row it is not clear how to parameterize the array and get it type cast to an array of enums.

When I try:

const foobarray = ["BAR"];
await pool.query("UPDATE my_table SET foobarray=$2::foobar[] WHERE id=$1", [id, foobarray]);

I get:

error: invalid input value for enum foobarray: "{"

Any ideas how to get this to work?

Answer 1

I figured out my issue...

I was actually pulling the value, before updating it, as follows:

SELECT foobarray FROM my_table WHERE id=$1;

This resulted in the following array being in the result:

["{", "}", "FOO"]

I didn't realize this and was trying to modify the array from the result before updating which resulted in "{" and "}" which are obviously not valid ENUM values being passed through.

I was able to solve this issue by keeping my original UPDATE query the same but modifying the SELECT query to:

SELECT foobarray::text[] FROM my_table WHERE id=$1;

This results in the following array being in the result:

["FOO"]

Tweaking it and updating now causes no problems.