Sagit Khaliullin Sagit Khaliullin - 10 months ago 78
SQL Question

Update ENUM value with PreparedStatement java PostgreSQL

I have an SQL Enum:

CREATE TYPE position as ENUM ('G','W','D');

And I need to update this value using java PreparedStatement. I tried to do it in this way:

PreparedStatement ps = connection.prepareStatement("UPDATE players
SET position = ? WHERE id = 1");

ps.setString(1, "W");

But I get an org.postgresql.util.PSQLException: ERROR: syntax error at or near "position".

I also tried
position = ?::position
position = CAST(? AS position)
but I always get the same error.
Please help me to manage with that

Answer Source

You will need to quote the name of your enum when you cast:

        PreparedStatement stmt = con.prepareStatement("update players set position = ?::\"position\" where id = 1");

The syntax error is happening because POSITION is a keyword. Wrapping it in double quotes forces postgres to parse it as an identifier.

Probably a good idea to use a different name for the enum.