Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Saving a UUID through JDBC fails with a type mismatch

ErrorERROR: column "id" is of type uuid but expression is of type character varying

Short answer

pgJDBC sends setString parameters as varchar, and PostgreSQL will not store varchar in a uuid column without a conversion. Cast the placeholder in SQL with ?::uuid, or add stringtype=unspecified to the URL so the server infers the type. If the error is about syntax, the text itself is not a valid UUID.

  • Applies to PostgreSQL 16, PostgreSQL 17, pgJDBC 42, Java 21
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the full error: the second line says whether the type is wrong or the text itself is not a UUID.

    psql -c "SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;"
  2. 2Match what you see, then jump to its fix

    1. 1column is of type uuid but expression is of type character varyingThe UUID is bound as a stringFix
    2. 2The same error appears from many queries, often from a frameworkYou cannot change the SQL everywhereFix
    3. 3invalid input syntax for type uuidThe text is not a valid UUIDFix

What does the error look like?

  • ERROR: column "id" is of type uuid but expression is of type character varying
  • Hint: You will need to rewrite or cast the expression.
  • ERROR: invalid input syntax for type uuid

Cause 1 of 3

The UUID is bound as a string

1Check

pgJDBC binds setString values as varchar unless told otherwise. The stringtype property controls this.

grep -rn "setString" src/main/java/ | grep -i id

2Fix

Cast in SQL so the server knows the target type. This keeps the code explicit and needs no URL change.

PreparedStatement st = conn.prepareStatement(
    "INSERT INTO users (id, name) VALUES (?::uuid, ?)");
st.setString(1, idText);
st.setString(2, name);

3Verify

The insert succeeds and the row is stored.

SELECT id FROM users LIMIT 1;

Confirmed by jdbc.postgresql.org

Did this fix it?

Cause 2 of 3

You cannot change the SQL everywhere

1Check

Setting stringtype to unspecified makes pgJDBC send setString parameters as untyped values, and the server infers the type. It exists for code that uses setString for non-text columns.

grep -rn "jdbc:postgresql" src/main/resources/

2Fix

Add the parameter to the JDBC URL. It applies to every string parameter on that connection, so test queries that compare text.

jdbc:postgresql://localhost:5432/app?stringtype=unspecified

3Verify

Rerun the failing insert.

SELECT count(*) FROM users;

Confirmed by jdbc.postgresql.org

Did this fix it?

Cause 3 of 3

The text is not a valid UUID

1Check

PostgreSQL accepts the standard 8-4-4-4-12 form, upper case, braces, missing hyphens, and a hyphen after any group of four digits. Anything else, such as a trailing space or a 35-character value, is rejected.

SELECT length('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11');

2Fix

Trim and validate the value in Java before binding, for example with UUID.fromString, and log the original text when it fails.

UUID id = UUID.fromString(idText.trim());

3Verify

UUID.fromString no longer throws and the query runs.

SELECT 'a0eebc999c0b4ef8bb6d6bb9bd380a11'::uuid;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Keep UUID values as java.util.UUID inside the app and convert to text only at the edges.
  • Choose one fix per project, SQL cast or stringtype, so queries behave the same everywhere.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Databases

Java cannot load org.postgresql.Driver

Class not found loading JDBC org.postgresql.Driver

The PostgreSQL JDBC jar is not on the classpath of the process that runs your code.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.