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 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 as String.
The SQL array can be passed as Array[T], with T supported as parameter type.
You can also have a look at documentation about the multi-value parameters.
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.
