How to alter Postgres Enum type using JDBC

Question

I try to add values to a postgres enum type like this (removing the loop for the values):

      PreparedStatement stmt = conn.prepareStatement("ALTER TYPE my_enum_type ADD VALUE ?;");
      
      stmt.setString(1, "value");
      stmt.addBatch();
      stmt.executeBatch()
            

When I run that I get:

Batch entry 0 ALTER TYPE my_enum_type ADD VALUE 'value' was aborted: ERROR: syntax error at or near "$1"

googling I found out that it probably has to do with casting. I changed the query to:

ALTER TYPE my_enum_type ADD VALUE ?::my_enum_type

it gives the same error.

and

ALTER TYPE my_enum_type ADD VALUE cast(? as my_enum_type)

it fails with syntax error on cast

I worked around it by concatenating a string with the enum value and executing that, but there has to be a better way.

I am new to postgres, is there a simple way to work around it?

EDIT: In response to comments and to avoid confusion:

1. How do I make Java & Postgres enums work together for update? is about executing updates and not ALTER. The solution there is to cast to the target type, which I tried and did not work.

Solution:

Following Laurenz Albe's comment I ended up modifying my solution to:

        try (Statement stmt = conn.createStatement()) {

            PGConnection pgconn = conn.unwrap(PGConnection.class);

            for (String i : missingValues) { 

                stmt.addBatch("ALTER TYPE my_enum_type ADD VALUE '" + pgconn.escapeLiteral(i) + "'");
            }


            stmt.executeBatch();
        }

Answer 1

Parameters in statements can only be used with INSERT, SELECT, UPDATE, DELETE, MERGE and VALUES, but not with ALTER TYPE.

To find this limitation in the documentation, you have to know that a JDBC prepared statement in time leads to a “server-side prepared statement”, which in PostgreSQL can be created with the SQL statement PREPARE. The documentation for PREPARE says:

statement

Any SELECT, INSERT, UPDATE, DELETE, MERGE, or VALUES statement.

You'll have to construct the query string yourself. To avoid SQL injection, quote the string properly with the built-in methods:

org.postgresql.PGConnection pgconn = conn.unwrap(org.postgresql.PGConnection.class);

String query = "ALTER TYPE my_enum_type ADD VALUE " + pgconn.escapeLiteral("value");

conn.createStatement().executeUpdate(query);