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.

"operator does not exist: text = uuid" means your column is a text column. You need to compare the same types, e.g. `id::uuid = '...'::uuid` or `id::text = '....'` — , Jul 20 '20 at 11:14
I understand that error. I don't understand the opposite. i.e. `operator does not exist: uuid = character varying` when the column is a `UUID`. To be clear I get that it's telling me the types don't match. But I don't get why the types are compared at all as they don't seem to be with `psql` or with the flat sql statement above. In this case I don't want the types to matter. Similar to how the insert does not care whether the column is a `UUID` or `TEXT`. It just stores the info passed in. — Shane Gannon, Jul 20 '20 at 12:37
The same way you can't compare a `java.lang.String` instance with a `java.util.UUID` instance you can't compare a `uuid` column with a `text` (or `varchar`) column. — , Jul 20 '20 at 12:46
Does that mean the `jdbc` library is checking the column's type and the argument's type and throwing an exception when they don't match? I'm trying to understand why I don't see this error when running the same SQL via `psql` or when I bake the arguments into the sql string. — Shane Gannon, Jul 20 '20 at 13:00
Partially, I assume your JDBC library uses `setString()` or `setObject()` depending on the type of parameter which results in the error. Same happens if you explicitly try to compare the wrong types in plain SQL: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=682799e0a7a1c03d25a412dfd5c55e5a — , Jul 20 '20 at 13:03
I can understand `'00000000-0000-0000-0001-000000000000'::text` giving an error. But the error reports the sql command was missing `::text`. i.e. `'00000000-0000-0000-0001-000000000000'`. As such I assumed the behaviour would match the `psql` cmd. The library in question is https://github.com/brettwooldridge/HikariCP. Is there a technic to work around column type changes in scenarios where you can't guarantee the db and code will change at the same time? Trying to figure out if taking the above baked in string approach is an acceptable practice. — Shane Gannon, Jul 20 '20 at 13:25

score 0 · Answer 1 · answered Jul 20 '20 at 16:12

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.

BadSqlGrammarException with namedParameterJdbcTemplate but not psql

1 Answers1