1

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!

Lauris
  • 345
  • 2
  • 8

2 Answers2

3

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

Community
  • 1
  • 1
Janick Bernet
  • 20,544
  • 2
  • 29
  • 55
  • Thanks. What would be the preferred way to pass "IS NULL" from user side? The simplest way I can imagine is just plain old string interpolation, but that doesn't seem like the best way. – Lauris Jul 24 '14 at 12:59
  • Yep, string. It's part of the query expression, so if you want to alternate between "IS NULL" and "IS NOT NULL" based on some condition, that's probably the best way to go. – Janick Bernet Jul 24 '14 at 13:00
  • build your prepared statement using a condition. In cases you want to test on `NULL` use `IS NULL`, in other cases compose prepared statement with `IS NOT NULL`. –  Jul 24 '14 at 13:01
  • Thanks, makes sense. Luckily I can use query builder and just alternate between ``.where("foo IS NULL")`` and ``.where("foo IS NOT NULL")`` (pseudo-code). – Lauris Jul 24 '14 at 13:05
2

@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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This is an interesting insight, thanks for the tip! Actually I need to support different database backends, e.g., MySQL, Postgres and SQL Server. – Lauris Jul 25 '14 at 18:19
  • Actually, both solutions are standard SQL: http://stackoverflow.com/questions/10416789/how-to-rewrite-is-distinct-from-and-is-not-distinct-from. Sadly, not all RDBMS try to implement standard features as ambitiously as Postgres ... – Erwin Brandstetter Jul 25 '14 at 18:25