Search code examples
postgresqljdbchikaricp

How to set transform_null_equals postgres parameter on a connection?

First some context in case it helps with your understanding of the why piece and sometimes leads to other good answers. I love this settings transform_null_equals because instead of

  • sql column value=NULL where I am told null means unknown
  • sql WHERE clause value=null where null means null

The setting in the title basically changes postgres so that null in WHERE clause AND column value BOTH mean 'unknown'. I can then say WHERE c.col=null (which means find any columns WHERE c.col is unknown) and I can also do WHERE c.col="value"

In this way in null languages, I can then do c.col=variable and variable can be null for unknown and a value for something that is known. perfect!

I realize this is a violation of the spec but it makes our team super fast(which is way more important in our business)....we have had less bugs, and WAY WAY simpler queries....OMG, way simpler.

Now, we set this on the user, but I want to set this via the connection instead so when someone installs postgres, it just magically works without them having to remember to set the setting.

  1. How to do in jdbc?
  2. Even better, How to do in Hikari Pool
Solution

  • You may have less trouble writing your queries with transform_null_equals, but I doubt that they will perform better, since this will just replace = NULL with IS NULL before the query is optimized.

    Anyway, you can use the options parameter in a JDBC connection string to supply the parameter to the server process:

    jdbc:postgresql:dbname?user=myuser&password=mypwd&options=-ctransform_null_equals%3Don