BadSqlGrammarException with namedParameterJdbcTemplate but not psql

Question

I'm altering a Postgres column's type from UUID to TEXT with

ALTER TABLE my_table ALTER COLUMN id TYPE TEXT;

But I want to ensure my code works with both column types. i.e. As it's difficult to sync up the alteration of the db with the code which runs on a server.

I'm testing this locally by switching between the types. This can be done by moving back to UUID (while the data is in the correct format) with

ALTER TABLE my_table ALTER COLUMN id TYPE UUID USING id::uuid;

This works. But when switched to a TEXT and running my Java code I noticed some failures in the tests. Specifically whenever a WHERE statement makes use of the switched id. e.g.

UPDATE my_table SET name = 'new_name' WHERE id = '00000000-0000-0000-0001-000000000000'::uuid;

generates

ERROR:  operator does not exist: text = uuid

That makes sense to me as the namedParameterJdbcTemplate class I'm using is adding a ::uuid to the end of the id in the SQL query. I'm not certain why it bothers doing that but it can be worked around by converting the passed in parameter from a UUID to a string.

However if I switch the column back to UUID I get the following error with

UPDATE my_table SET name = 'new_name' WHERE id = '00000000-0000-0000-0001-000000000000';

generating

ERROR:  operator does not exist: text = uuid

I can accept the first update query causing issues because I can run it on the psql command line and get the same error. However this does not happen with the second command in psql.

Furthermore if I stop using the namedParameterJdbcTemplate's parameters and bake the values into the sql string it works.

String sqlStatement = "UPDATE my_table SET name = :v_new_name WHERE id = :v_id";

MapSqlParameterSource sqlParameterMap = new MapSqlParameterSource();
sqlParameterMap.addValue("v_new_name", "New Name");
sqlParameterMap.addValue("v_id", id.toString());

namedParameterJdbcTemplate.update(sqlStatement, sqlParameterMap);

Generates

ERROR: operator does not exist: uuid = character varying

thrown from SQLStateSQLExceptionTranslator.java.

versus

String sqlStatement = "UPDATE my_table SET name = 'New Name' WHERE id = '" + id.toString() + "'";

MapSqlParameterSource sqlParameterMap = new MapSqlParameterSource();

namedParameterJdbcTemplate.update(sqlStatement, sqlParameterMap);

which works.

Why is that the case? It feels like the namedParameterJdbcTemplate is doing some extra type checking that I cannot find.

Answer 1

Frustrated with the idea of having to bake the variables into the SQL statement I took a guess and tried the following

String sqlStatement = "UPDATE my_table SET name = :v_new_name WHERE id = :v_id";

MapSqlParameterSource sqlParameterMap = new MapSqlParameterSource();
sqlParameterMap.addValue("v_new_name", "New Name");
sqlParameterMap.addValue("v_id", id.toString(), java.sql.Types.OTHER);

namedParameterJdbcTemplate.update(sqlStatement, sqlParameterMap);

To my surprised it worked. Before I had experimented with the differences between Types.BINARY and Types.VARCHAR. But I guess I needed to be less specific. The definition of OTHER states

The constant in the Java programming language that indicates that the SQL type is database-specific and gets mapped to a Java object that can be accessed via the methods getObject and setObject.

Which sounds like an appropriate default value but it seems the library does not use it as such.

I do have to note that this only works with id.toString(). Using id by itself leads to a type error.

Thanks to some insights from @a_horse_with_no_name which helped.