23

The latest Java JDBC drivers for postgres claim to support UUIDs natively; working against Postgres 9.2 (mac).

Indeed, when a PreparedStatement is used, I can step through the driver code, and even walk through the specialised 'setUuid' function in AbstractJdbc3gStatement.java. By all indications, it should 'just work'.

However, it does not work. The database flings back an error, which I receive thus:

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = bytea
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 139
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1002.jdbc4.jar:na]

Yes, indeed, setUuid in the JDBC driver does send that as a bytea :

private void setUuid(int parameterIndex, UUID uuid) throws SQLException {
        if (connection.binaryTransferSend(Oid.UUID)) {
            byte[] val = new byte[16];
            ByteConverter.int8(val, 0, uuid.getMostSignificantBits());
            ByteConverter.int8(val, 8, uuid.getLeastSignificantBits());
            bindBytes(parameterIndex, val, Oid.UUID);
        } else {
            bindLiteral(parameterIndex, uuid.toString(), Oid.UUID);
        }
    }

What gives? Is there some magic rune required in the actual database to bless this conversion ?

Improve this question
1
  • 1
    Can you post the actual PreparedStatement you are trying to run? Commented Sep 18, 2013 at 15:13

4 Answers 4

Reset to default
42

tl;dr

Use index-friendly UUID Version 7 (Unix-style timestamp plus random bits):

myPreparedStatement.setObject
( 
    … , 
    com.github.f4b6a3.uuid.UuidCreator.getTimeOrderedEpoch()  // "UUID Creator" library.
)

… or use another library:

myPreparedStatement.setObject
( 
    … , 
    com.fasterxml.uuid.Generators.timeBasedEpochGenerator().generate()  // "UUID Creator" library.
)

If no Java library available supporting UUID Version 7, fall back to using UUID Version 4 (random bits, not index-friendly):

myPreparedStatement.setObject
( 
    … , 
    java.util.UUID.randomUUID()
)

Details

(a) Show us your code.

PreparedStatement::setObject does work when passing a java.util.UUID. You likely have some other issue in your code.

(b) See my blog post UUID Values From JDBC to Postgres for a bit of discussion and example code.

// Example data to store in database.
java.util.UUID uuid = java.util.UUID.randomUUID(); // Generate a random UUID. 
String foodName = "Croissant";

// JDBC Prepared Statement.
PreparedStatement preparedStatement = conn.prepareStatement( "INSERT INTO food_ (pkey_, food_name_  ) VALUES (?,?)" );
int nthPlaceholder = 1; // 1-based counting (not an index).
preparedStatement.setObject( nthPlaceholder++, uuid ); 
preparedStatement.setString( nthPlaceholder++, foodName ); 

// Execute SQL.
if ( !( preparedStatement.executeUpdate() == 1 ) ) { 
  // If the SQL reports other than one row inserted…
  this.logger.error( "Failed to insert row into database." );
}

(c) I'm not sure what you mean by

The latest Java JDBC drivers for postgres claim to support UUIDs natively

Which driver? There are at least two open-source JDBC drivers for Postgres, the current/legacy one and a new rewrite "next generation" one. And there are other commercial drivers as well.

"natively"? Can you link to the documentation you read? The SQL spec has no data type for UUID (unfortunately ☹), therefore the JDBC spec has no data type for UUID. As a workaround, the JDBC driver for Postgres uses the setObject and getObject methods on PreparedStatement move the UUID across the chasm between Java ↔ SQL ↔ Postgres. See the example code above.

As the PreparedStatement JDBC doc says:

If arbitrary parameter type conversions are required, the method setObject should be used with a target SQL type.

Perhaps by "natively", you confused Postgres' native support for UUID as a data type with JDBC having a UUID data type. Postgres does indeed support UUID as a data type, which means the value is stored as 128-bits rather than multiple times that if it were stored as as ASCII or Unicode hex string. And being native also means Postgres knows how to build an index on a column of that type.

The point of my blog post mentioned above was that I was pleasantly surprised by how simple it is to bridge that chasm between Java ↔ SQL ↔ Postgres. In my first uneducated attempts, I was working too hard.

Postgres support of UUID

Another note about Postgres supporting UUID… Postgres knows how to store, index, and retrieve existing UUID values.

Storing UUID values is built into Postgres for all versions, while generating UUID values has more of a story:

  • In older versions of Postgres, to generate UUID values you must enable the Postgres extension (plugin) uuid-ossp. This extension wraps a library provided by The OSSP Project for generating a variety of kinds of UUID values. See my blog for instructions.
  • In modern Postgres, generate UUID values using the built-in functions uuidv7 & uuidv4 methods described below.

Modern UUID

Update on modern UUIDs… In 2024, the IETF finalized RFC 9562, obsoleting RFC 4122, a new specification for UUID.

This new spec invents three new Versions of UUID, inspired by dozens of experimental efforts in the community. In addition to Versions 1 (point in time and space), 4 (random), and 5 (name-based, SHA-1 hash) already in use, we now have Versions 6 (point in time and space), 7 (time plus random), and 8 (undefined, for experimental and private use).

  • Version 1 (point in time and space)
  • Version 4 (random)
  • Version 5 (name-based, SHA-1 hash, supplants Version 3)
  • 👉🏽 Version 6 (point in time and space of Version 1 but with sensible bit-layout)
  • 👉🏽 Version 7 (Unix time plus random)
  • Version 8 (undefined, for experimental and private use)

Index efficiency

The purpose of Versions 6 & 7 is to be index-friendly. The older UUIDs are index-hostile, making inserts to a database significantly inefficient in larger tables. That index-inefficiency problem is resolved by using Versions 6 & 7.

  • Version 6 is a reworking of Version 1, with the same fields but rearranged more sensibly to put most significant bits first. You can refactor a database to convert between 1 and 6, lossless and reversible, to dramatically improve performance.
  • Version 7 is a new kind of UUID. It uses a different kind of timestamp than Versions 1/6. V.7 uses a Unix-style timestamp, a count since first moment of 1970 in UTC, 1970-01-01T00:00Z. By default the count is milliseconds with an allowance for using up to 12 more bits for a sub-millisecond timestamp. The remaining bits are randomly-generated. Prefer Version 7 over 6 & 1 if possible.

Postgres 18+

Postgres 18 (released 2025-09) adds support for Version 7 by adding a built-in generation function, uuidv7. This function exercises that spec-granted option to use 12 more bits for sub-millisecond timestamp.

And, in Postgres 18+, the existing random UUID (Version 4) generation function gets a second name for consistency: uuidv4. With these two functions built-in, you may no longer need the uuid-ossp extension.

Java

On the JDBC side, Java has not been updated for RFC 9562. So the java.util.UUID class does not understand Versions 6 and 7. But the class is compatible nevertheless! The fromString blindly parses the hex string inputs of Version 6 and 7, converting them to 128 bits appropriately. The variant and version methods report correct values. But the timestamp() & node() methods do not yet work with a Version 6 or 7 UUID object (as of Java 25).

As for generating Version 6 or 7 values in Java, you’ll need a third-party library. At least two such libraries exist that have been updated to support RFC 9562. Both of those libraries return java.util.UUID objects.

I am not specifically recommending either library as I have not yet examined their source code nor have I used them in production. Do you own due diligence.

Access them as a dependency in Maven, Gradle, etc.

<!-- UUID Creator -->
<!-- https://github.com/f4b6a3/uuid-creator -->
<dependency>
    <groupId>com.github.f4b6a3</groupId>
    <artifactId>uuid-creator</artifactId>
    <version>6.1.1</version>
</dependency>

<!-- Java Uuid Generator (JUG) -->
<!-- https://github.com/cowtowncoder/java-uuid-generator -->
<dependency>
    <groupId>com.fasterxml.uuid</groupId>
    <artifactId>java-uuid-generator</artifactId>
    <version>5.1.0</version>
</dependency>

Example code

So the modern version of the Java code seen above would be like the following. I put in calls to both libraries listed above, so delete the unwanted line after cut-and-paste.

// Example data to store in database.
// Generate a Version 7 UUID based on Unix-style timestamp plus random bits.
java.util.UUID uuid =  com.github.f4b6a3.uuid.UuidCreator.getTimeOrderedEpoch();  // "UUID Creator" library.
java.util.UUID uuid =  com.fasterxml.uuid.Generators.timeBasedEpochGenerator().generate();  // "Java Uuid Generator (JUG)" library.
String foodName = "Croissant";

// Prepared statement in SQL.
PreparedStatement preparedStatement = conn.prepareStatement( "INSERT INTO food_ (pkey_, food_name_  ) VALUES (?,?)" );
int nthPlaceholder = 1; // 1-based counting (not an index).
preparedStatement.setObject( nthPlaceholder++, uuid ); 
preparedStatement.setString( nthPlaceholder++, foodName ); 

// Execute SQL.
if ( !( preparedStatement.executeUpdate() == 1 ) ) 
{ 
    // If the SQL reports other than one row inserted…
    this.logger.error( "Failed to insert row into database." );
}

In a rapid-fire scenario where you are generating many UUIDs per millisecond, use the UUID Creator library, and change the method called above to UuidCreator.getTimeOrderedEpochPlus1() or getTimeOrderedEpochPlusN. Those methods generate a monotonic number within each timestamp (each millisecond) to (a) guarantee uniqueness, and for (b) speedier performance (random generation is more work than incrementing). The other library may not offer this feature (I am not certain).

SQL Standard

By the way…

If I knew how to petition the JDBC expert group or JSR team to make JDBC aware of UUID, I certainly would. They are doing just that for the new date-time types being defined in JSR 310: Date and Time API.

Similarly, if I knew how to petition the SQL standards committee to add a data type of UUID, I would. But apparently that committee is more secretive than the Soviet Politburo and slower than a glacier.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

> that committee is more secretive than the Soviet Politburo and slower than a glacier. ... Comment of the year !!!
18

I used the following approach to add UUID and other objects to postgres:

 PGobject toInsertUUID = new PGobject();
 toInsertUUID.setType("uuid");
 toInsertUUID.setValue(uuid.toString());
 PreparedStmt stmt = conn.prepareStatement(query);
 stmt.setObject(placeHolder,toInsertUUID);
 stmt.execute();

This way you will be stopping yourself from doing type casting. This piece of code worked perfectly for me for any time for example even json.

Improve this answer

2 Comments

where does PGobject come from ?
@PaulTaylor, org.postgresql.util.PGobject comes from PostgreSQL JDBC driver, org.postgresql:postgresql.
15

This worked for me using the org.postgresql.postgresql 42.2.5

myPreparedStatement.setObject(4, UUID.randomUUID(),java.sql.Types.OTHER)

Without java.sql.Types.OTHER I got an error

Improve this answer

1 Comment

Thank you so much, I've had a real nightmare of a time trying to find the right solution to this.
-1

try 

.setParameter("uuid", uuid, PostgresUUIDType.INSTANCE);
Improve this answer

2 Comments

Where does PostgresUUIDType.INSTANCE come from, seems to be Hibernate class ?
org.hibernate.type

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.