java - PSQLException: ERROR: syntax error at or near "$1"

Question

Am I using the syntax incorrectly in the PreparedStatement?

When executing:

1    statement = conn.prepareStatement("insert into ?.? (?) values(?)");
2    statement.setString(1, schemaName);
3    statement.setString(2, tableName);
4    statement.setString(3, columnName);
5    statement.setString(4, columnValue);
6    statement.executeUpdate();
7    conn.commit();

The exception thrown:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
Position: 13
    at  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
    at PostgreSQLDatabase.updateTable(MyClass.java:6)

Answer 1

Identifiers (such as table and column names) cannot be used with placeholders so you have to use string operations instead and only use a placeholder for the values(?):

statement = conn.prepareStatement("insert into " + schemaName + "." + tableName + " (" + columnName + ") values(?)");
statement.setString(1, columnValue);

This assumes that you've chosen schemaName, tableName, and columnName so that they're not case sensitive or contain spaces or similar oddities. If the identifiers are case sensitive or contain spaces and such then you'd need to double quote them in the SQL:

"insert into \"" + schemaName + "\".\"" + ...

If the identifiers can also contain double quotes then you'll have to double them before putting them in the SQL.

---

The error talks about $1 because PostgreSQL uses numbered placeholders ($1, $2, ...) natively and JDBC is translating the ? placeholders to numbered ones internally.

Answer 2

try this

statement = conn.prepareStatement("insert into " + schemaName + ".? (?) values(?)");
statement.setString(1, tableName);
statement.setString(2, columnName);
statement.setString(3, columnValue);
statement.executeUpdate();
conn.commit();