Play Anorm insert a scala List into a postgres text array column

Question

I'm trying to insert a List[String] into a postgresql column of type text[]. I believe when you attempt to insert any List, Anorm inserts each member of the List in its own column. I'm pretty sure this is the case because I get back the exception:

org.postgresql.util.PSQLException: ERROR: INSERT has more expressions than target columns

What I want to do is insert the entire List as a text[]. My current code:

def insertList(listName: String, subLists: List[String]): Long = {
DB.withConnection{implicit c =>
SQL(
  """
    INSERT INTO mailinglists(name, sublists) VALUES({listName}, {subLists})
  """)
  .on('listName -> listName, 'subLists -> subLists)
  .executeInsert(scalar[Long] single)
}
}

I tried to go down this path:

ConnectionPool.borrow().createArrayOf("text", subLists.toArray)

But I get the error:

type mismatch;
found   : (Symbol, java.sql.Array)
required: anorm.NamedParameter

Answer 1

The code that ended up working for this issue was:

def insertList(listName: String, subLists: List[String]): Long = {
DB.withConnection{implicit c =>
  SQL"INSERT INTO mailinglists(name, sublists) VALUES($listName, ARRAY[$subLists])"
  .executeInsert(scalar[Long] single)
}
}

What changed from the original post was the use of Anorm string interpolation SQL"..." and the addition of ARRAY[...] around the multi-value parameter. I'm not exactly sure why this worked as the postgres exceptions were very mysterious.