CODEWITHSUNDEEP
javasqlpostgresqlenumsprepared-statement
Sagit Khaliullin
Sagit Khaliullin

Reputation: 184

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 or position = CAST(? AS position) but I always get the same error. Please help me to manage with that

Upvotes: 0

Views: 3160

Answers (3)

abbas
abbas

Reputation: 7081

Have you tried this

ps.setObject(1, "W", Types.OTHER);

Upvotes: 1

teppic
teppic

Reputation: 7286

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.

Upvotes: 2

Swapan Pramanick
Swapan Pramanick

Reputation: 175

This is because position is a function already declared in Postgres. Please use any different name. No casting is required.

Upvotes: 1

Related Questions