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

cchantep · Accepted Answer · 2015-12-26 18:34:38Z

2

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.

edited Dec 26, 2015 at 18:34

cchantep

9,1763 gold badges32 silver badges43 bronze badges

answered Dec 26, 2015 at 17:49

plamb

5,6361 gold badge20 silver badges31 bronze badges

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

Comments

cchantep · Accepted Answer · 2015-12-24 01:03:49Z

1

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.

edited Dec 24, 2015 at 1:03

answered Dec 23, 2015 at 23:32

cchantep

9,1763 gold badges32 silver badges43 bronze badges

6 Comments

plamb Over a year ago

So i transformed the List using .toArray which resolved, but now I'm getting " org.postgresql.util.PSQLException: Unable to find server array type for provided name VARCHAR". The column its being inserted into is a text[].

plamb Over a year ago

Is anorm passing some typecast "VARCHAR" when it should be passing "varchar" ?

plamb Over a year ago

Here you can see anorm using "VARCHAR" as the "sqlType" and jdbcType github.com/playframework/anorm/blob/…

cchantep Over a year ago

PostgreSQL doesn't matter the case. Your issue is elsewhere. You can try multi-value SQL"INSERT INTO mailinglists(name, sublists) VALUES($listName, ARRAY[$subLists])"

plamb Over a year ago

So the key to getting passed the VARCHAR exception was switching my SQL to string interpolation. However, now I'm hitting another mysterious psql exception. If i run it with the code you suggested, INSERT INTO mailinglists(name, sublists) VALUES($listName, ARRAY[$array]) I get ` org.postgresql.util.PSQLException: ERROR: syntax error at or near ";" Position: 86` If i execute it without ARRAY[...] I get: ` org.postgresql.util.PSQLException: ERROR: syntax error at or near "[" Position: 62` Neither SQL statement contains the problematic char in the exception

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related