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
The code that ended up working for this issue was:
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.The values passed as parameters to Anorm are not supposed to be raw JDBC values. You should not pass
java.sql.*
values; You can have a look at the parameter mappings.Then you are passing parameter names as
Symbol
, which has been deprecated in Anorm 2.3 and is not longer supported since 2.4. Names must be passed asString
.The SQL array can be passed as
Array[T]
, withT
supported as parameter type.You can also have a look at documentation about the multi-value parameters.