Thread: Inserting into a uuid column

Hi,

I have a table with a column of type UUID and I would like to import data into
that table.

I'm using a Java based import tool that will use a PreparedStatement and
setObject() (passing a String variable) to insert the data.

The problem is that the import does not work because Postgres throws an error:

"ERROR: column "guid" is of type uuid but expression is of type character varying"

What I don't understand is that doing an insert with a character literal:

INSERT INTO my_table (guid_column, ...)
VALUES
('a0eebc999c0b4ef8bb6d6bb9bd380a11', ...)

works without problems also when used from within Java.

But the following Java code will throw the above error:

PrparedStatement stmt = connection.prepareStatement(
     "insert into my_table (guid_column) values (?)");
stmt.setObject(1, "a0eebc999c0b4ef8bb6d6bb9bd380a11");
stmt.executeUpdate();


To me this sounds like a problem with the JDBC driver, or am I missing something?

I'm using PG 8.3.5 on Windows XP, the driver is postgresql-8.3-603.jdbc3.jar

Regards
Thomas

On Tue, 3 Mar 2009, Thomas Kellerer wrote:

> I have a table with a column of type UUID and I would like to import data
> into that table.
>
> I'm using a Java based import tool that will use a PreparedStatement and
> setObject() (passing a String variable) to insert the data.
>
> "ERROR: column "guid" is of type uuid but expression is of type character
> varying"

You should use setObject(<column>, <string value>, Types.OTHER) to
indicate that while you are passing a String, you aren't expecting the
server type to be a string datatype.

Alternatively, you can use the 8.4dev JDBC4 driver and issue:

setObject(<column>, <java.util.UUID object>);

> What I don't understand is that doing an insert with a character literal:
>
> INSERT INTO my_table (guid_column, ...)
> VALUES
> ('a0eebc999c0b4ef8bb6d6bb9bd380a11', ...)

Here you aren't providing any specific type information, just a literal.
By saying setString or setObject with a String parameter you are saying
that it really is a String.

Kris Jurka

Kris Jurka wrote on 03.03.2009 22:13:
> You should use setObject(<column>, <string value>, Types.OTHER) to
> indicate that while you are passing a String, you aren't expecting the
> server type to be a string datatype.

The problem is that this is a generic import tool, but I'll see what I can do.

>> INSERT INTO my_table (guid_column, ...)
>> VALUES
>> ('a0eebc999c0b4ef8bb6d6bb9bd380a11', ...)
>
> Here you aren't providing any specific type information, just a literal.
> By saying setString or setObject with a String parameter you are saying
> that it really is a String.

No I'm not using setString() in that example. The Java code would be:

Statement stmt = connection.createStatement();
stmt.executeUpdate("INSERT INTO my_table (guid_column) " +
" VALUES ('a0eebc999c0b4ef8bb6d6bb9bd380a11')");

So it's passing a literal and is not using a PreparedStatement

The Javadocs of setObject(int, Object) say:

"The given argument will be converted to the corresponding SQL type before being
sent to the database"

So I was expecting that the driver will be able to do the same conversion with
the PreparedStatement as it is obviously happening when using a literal (though
that conversion probably takes place on the server not in the driver).

Regards
Thomas

Thomas Kellerer wrote:

> No I'm not using setString() in that example. The Java code would be:
>
> Statement stmt = connection.createStatement();
> stmt.executeUpdate("INSERT INTO my_table (guid_column) " +
> " VALUES ('a0eebc999c0b4ef8bb6d6bb9bd380a11')");
>
> So it's passing a literal and is not using a PreparedStatement

That should work identically to what you're doing via psql then, so
something strange is going on. Can you put together a testcase showing
the problem?

> The Javadocs of setObject(int, Object) say:
>
> "The given argument will be converted to the corresponding SQL type
> before being sent to the database"

The "corresponding SQL type" of a java.lang.String is Types.VARCHAR.

> So I was expecting that the driver will be able to do the same
> conversion with the PreparedStatement as it is obviously happening when
> using a literal (though that conversion probably takes place on the
> server not in the driver).

If you were using a PreparedStatement (which you're not anyway) then
it's more like executing 'PREPARE foo(varchar) AS INSERT INTO ....
VALUES ($1)' -- the driver does not substitute query values inline, but
uses the server's support for providing parameters and their types
out-of-line. There is some magic in the query parser that treats string
literals more as an unknown type to be inferred from the query context,
rather than a specific type. Specifying Types.OTHER to setObject() is
the way to tell the JDBC driver that the String you passed isn't
necessarily a text type and the same type inference should be done.

-O

On Tue, 3 Mar 2009, Thomas Kellerer wrote:

> Kris Jurka wrote on 03.03.2009 22:13:
>> You should use setObject(<column>, <string value>, Types.OTHER) to indicate
>> that while you are passing a String, you aren't expecting the server type
>> to be a string datatype.
>
> The problem is that this is a generic import tool, but I'll see what I can
> do.

The other option is to use the URL parameter stringtype=unspecified if you
cannot change the underlying code.

http://jdbc.postgresql.org/documentation/83/connect.html#connection-parameters

> So I was expecting that the driver will be able to do the same conversion
> with the PreparedStatement as it is obviously happening when using a literal
> (though that conversion probably takes place on the server not in the
> driver).

The reason the driver doesn't do this is twofold.

1) The driver doesn't know the target server type.  Retrieving that would
induce an extra network roundtrip for execution.

2) Sometimes the server doesn't know the the target type and it's better
for the driver/user to provide it.  Consider the call of an overloaded
function: SELECT myfunc(?), if it has an implementation for both float and
int, you want the user/driver to indicate which one should be called
because the server doesn't know.

Kris Jurka

Oliver Jowett wrote:
> Thomas Kellerer wrote:
>
>> No I'm not using setString() in that example. The Java code would be:
>>
>> Statement stmt = connection.createStatement();
>> stmt.executeUpdate("INSERT INTO my_table (guid_column) " +
>> " VALUES ('a0eebc999c0b4ef8bb6d6bb9bd380a11')");
>>
>> So it's passing a literal and is not using a PreparedStatement
>
> That should work identically to what you're doing via psql then, so
> something strange is going on. Can you put together a testcase showing
> the problem?

Oh, sorry, I misread your email - this one works, so it's behaving as
expected :)

-O

Hi Kris,

thanks for your answers.

I can change the import to use setObject(int, Object, int) which works fine.

> The reason the driver doesn't do this is twofold.
>
> 1) The driver doesn't know the target server type.  Retrieving that
> would induce an extra network roundtrip for execution.
>
> 2) Sometimes the server doesn't know the the target type and it's better
> for the driver/user to provide it.  Consider the call of an overloaded
> function: SELECT myfunc(?), if it has an implementation for both float
> and int, you want the user/driver to indicate which one should be called
> because the server doesn't know.

I can see the problems there ;)

Regards
Thomas