Introduction

Using Spring’s NamedParameterJdbcTemplate and binding null values with a SQL type code and type name may lead to the pgJDBC driver performing costly metadata queries to infer internal types. This is by JDBC specification and pgJDBC design, but it’s good to know for performance reasons as it may go unnoticed. This post provides a few tips on how to avoid such queries entirely.

Problem

Any application or framework that use PreparedStatement.setNull(int,int,String) is candidate to these meta queries. The javadoc states:

void setNull(int parameterIndex, int sqlType, String typeName) throws SQLException

Sets the designated parameter to SQL NULL. This version of the method setNull should be used for user-defined types and REF type parameters. Examples of user-defined types include: STRUCT, DISTINCT, JAVA_OBJECT, and named array types.

Note: To be portable, applications must give the SQL type code and the fully-qualified SQL type name when specifying a NULL user-defined or REF parameter. In the case of a user-defined type the name is the type name of the parameter itself. For a REF parameter, the name is the type name of the referenced type. If a JDBC driver does not need the type code or type name information, it may ignore it. Although it is intended for user-defined and Ref parameters, this method may be used to set a null parameter of any JDBC type. If the parameter does not have a user-defined or REF type, the given typeName is ignored.

The SHOULD phrase means you can use it for any column type, including primitives and JSONB for example. Typically, the pgJDBC implementation lways attempts to resolve the internal type when both the sqlType and typeName is specified. In case of ARRAY, it also attempts to resolve the array element type, which is another even more involved meta data query. This repeats for every parameter bind operation, so it can really amplify write latency in the worst case.

A metadata query can look like:

SELECT pg_type.oid, typname
 FROM pg_catalog.pg_type LEFT
   JOIN (SELECT ns.oid AS nspoid, ns.nspname, r.r
 FROM pg_namespace AS ns
   JOIN (SELECT s.r, (current_schemas(_))[s.r] AS nspname
 FROM ROWS
 FROM (generate_series(_, array_upper(current_schemas(_), _))) AS s (r)) AS r USING (nspname)) AS sp
    ON sp.nspoid = typnamespace
   WHERE typname = _
 ORDER BY sp.r, pg_type.oid DESC
 LIMIT _

To demonstrate this, the following code sample passes null for the “price” and “description” columns. If null values are passed along with a column type constant and a type name, then the above query is sent by the driver to resolve the internal PG type.

MapSqlParameterSource namedParameters = new MapSqlParameterSource()
        .addValue("id", UUID.randomUUID())
        .addValue("inventory", 99)
        .addValue("name", "product-x99")
        .addValue("sku", "X-99")
        .addValue("price", null, Types.DECIMAL, "decimal")
        .addValue("description", null, Types.OTHER, "jsonb");

new NamedParameterJdbcTemplate(jdbcTemplate)
   .update("insert into product (id, inventory, name, description, price, sku) " +
           "values (:id,:inventory,:name,:description,:price,:sku)", namedParameters);

Solution

• Prefer indexed placeholders (?,?,..) over named parameter placeholders, thus avoiding the NamedParameterJdbcTemplate problem entirely

• Omit specifying the column type name. For example:

  • addValue("price", null)

  • addValue("description", null)

• If still binding a NULL value with a type name, use another type constant like Types.NULL:

  • addValue("price", null, Types.NULL, "decimal")

  • addValue("description", null, Types.NULL, "jsonb")

• Set the system property -Dspring.jdbc.getParameterType.ignore=true (source) to avoid similar situations with ARRAYs

How to verify

Set the pgJDBC and spring jdbc core namespace logger levels to TRACE.

<logger name="org.postgresql" level="TRACE"/>
<logger name="org.springframework.jdbc.core" level="TRACE"/>

The log output will be verbose but watch out for all pg_catalog queries before your actual INSERT or UPDATE statements:

2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl]   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@5c134052, maxRows=0, fetchSize=0, flags=17
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl]  FE=> Parse(stmt=null,query="SELECT pg_type.oid, typname   FROM pg_catalog.pg_type   LEFT   JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE typname = $1  ORDER BY sp.r, pg_type.oid DESC LIMIT 1",oids={1043})
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl]  FE=> Bind(stmt=null,portal=null,$1=<('jsonb')>,type=VARCHAR)
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl]  FE=> Describe(portal=null)
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl]  FE=> Execute(portal=null,limit=0)
2025-11-26 12:33:06.157 TRACE [o.postgresql.core.v3.QueryExecutorImpl]  FE=> Sync

Conclusion

Efficiently handling null values in Spring's NamedParameterJdbcTemplate is essential for optimizing database interactions and minimizing unnecessary overhead. This involves preferring indexed placeholders, omitting column type names, and configuring a system property to avoid costly metadata queries.