CODEWITHSUNDEEP
javasqlpostgresqlscalajdbc
Lauris
Lauris

Reputation: 345

JDBC error on IS NULL condition

I am using JDBC from Scala using ScalikeJDBC library.

Let's take a simple query: UPDATE "test_table" SET "test" = 1234 WHERE ("asdad" is null)

If I pass this query directly to psql or execute it raw via JDBC everything works fine. But if I try to pass the null as a parameter, I get the following error: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

I pass the null value like this: 

session.update("""UPDATE "test_table" SET "test" = 1234 WHERE ("asdad" is ?)""", null)

The ? placeholder gets automatically converted to $1 by ScalikeJDBC library. The executed query is shown in Scala console: 

   UPDATE "test_table" SET "test" = 1234 WHERE ("asdad" is null)

Thanks!

Upvotes: 1

Views: 576

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

@Janick gave a flawless explanation - except that his references are for SQL Server (which happen to agree, both use standard SQL in this case). Read the Postgres manual here.

You can still parameterize a single query with standard operators:

session.update("""UPDATE test_table
                  SET    test = 1234
                  WHERE  asdad = ?
                  OR    (asdad IS NULL) = ?
              )""", null, true);

And for any other value:

              ... , 123, false);

Be sure to observe operator precedence if you add more WHERE conditions.

IS NOT DISTINCT FROM

Better yet, use this:

session.update("""UPDATE test_table
                  SET    test = 1234
                  WHERE  asdad IS NOT DISTINCT FROM ?
              )""", null);

Works for NULL as well as for any other value.

Upvotes: 2

Janick Bernet
Janick Bernet

Reputation: 21184

A parameter is not allowed after is. You have to write IS NULL or IS NOT NULL as fixed expressions.

What is allowed would be = $1 and then set this placeholder to null, but that will not result in what you expect as a comparison with NULL is always unknown.

See related questions:

Why does NULL = NULL evaluate to false in SQL server

Is there any difference between IS NULL and =NULL

Upvotes: 3

Related Questions