Is a Postgres/SQL UPDATE statement limited to a maximum length?


Hi all,

I am using a sql update statement like this in a Groovy Out connector:

"sql.execute("UPDATE table SET col3=$bdmvar3, [and another 40 variables...].WHERE col1=$bdmvar1")"

The length of this statement is approximately 1300 characters. When I try to execute it, I get this syntax error:

Caused by: org.bonitasoft.engine.connector.exception.SConnectorException: java.util.concurrent.ExecutionException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "is" Position: 588

However, when I split it into 2 separate update statements (with each 20 variables and the same WHERE clause), it works fine.

Two lines further down the script I use an INSERT statement of about 1300 characters and that works fine...

Anyone any idea?

Thanks in advance,

1 answer

This one is the BEST answer!

There is no artificial limit, I've seen some test results at 20M SQL queries...

However is it simply possible that there is a syntax error in the statement as per the error? I know you say splitting it into two is OK, but no-matter-what two is not the same as one.

Have you fully debugged the statement (and executed it in the III client)?

Does it work there?


PS: If this reply answers your question, please mark a resolved.


Submitted by stefandebruijn on Sat, 01/09/2016 - 11:57

Thanks Seán. Your answer gave me just enough strength to continue debugging ;-)

The variables I am using to update the SQL row are bdm variables. Two of them were not assigned a value before (they shouldn't be in the update statement, I know :-)). I expected that they would be treated as null.

When I set these bdm variables with an empty string, or I leave them out the statement, it works.

Makes me still wonder though, why it was also working when I split the statement in two, but I think I am going to leave that for now :-)

Many thanks for your help,