SQLGrammerException : could not execute statement

Question

I am trying to alter a postgreSQL table using the following code.

String query = "ALTER TABLE \"georbis:world_boundaries\" DROP COLUMN testcolumn";
sessionFactory.openSession().createSQLQuery(query).executeUpdate();

The problem is my table name contains colon(:) due to which i'm getting the following error.

ERROR:  unterminated quoted identifier at or near ""georbis? DROP COLUMN testcolumn" at character 13
STATEMENT:  ALTER TABLE "georbis? DROP COLUMN testcolumn

Other answers of similar questions suggest that i should put double quotes(") around the table name which i tried but still getting the same error.

Also when i run the same query from SQL editor it works perfectly fine. I also tried to run the same query for a table that does not contain colon(:) and it worked .

Solution : Thanks everyone. Got the answer from HERE I changed my query like this and it worked.

String query = "ALTER TABLE \"georbis\\:world_boundaries\" DROP COLUMN testcolumn";

Answer 1

The problem is that ":" is a prompt for a named parameter. Executing the query directly in the ORM Session means the ORM will try to parse it and transform it.

Assuming you are using the latest Hibernate Session, you will need to execute the query at a lower level (jdbc connection), as this answer describes:

sessionFactory.openSession().doWork(
    new Work() {
        public void execute(Connection connection) throws SQLException 
        { 
            try(Statement stmt=connection.createStatement()){
                stmt.executeUpdate("ALTER TABLE \"georbis:world_boundaries\" DROP COLUMN testcolumn");
            }
        }
    }
);

That being said, are you absolutely sure you want to execute DDL from the ORM? There are other solutions out there. If your project is Maven, you can use SQL Maven plugin to put up then tear down SQL setups for testing.