Error: Column does not exist

Question

I have been able to link PostgreSQL to java. I have been able to display all the records in the table, however I unable to perform delete operation.

Here is my code:

con = DriverManager.getConnection(url, user, password);
String stm = "DELETE FROM hostdetails WHERE MAC = 'kzhdf'";
pst = con.prepareStatement(stm);
pst.executeUpdate(); 

Please note that MAC is a string field and is written in capital letters. This field does exist in the table.

The error that I am getting:

SEVERE: ERROR: column "mac" does not exist

Answer 1

When it comes to Postgresql and entity names (Tables, Columns, etc.) with UPPER CASE letters, you need to "escape" the word by placing it in "". Please refer to the documentation on this particular subject. So, your example would be written like this:

String stm = "DELETE FROM hostdetails WHERE \"MAC\" = 'kzhdf'";

On a side note, considering you are using prepared statements, you should not be setting the value directly in your SQL statement.

con = DriverManager.getConnection(url, user, password);
String stm = "DELETE FROM hostdetails WHERE \"MAC\" = ?";
pst = con.prepareStatement(stm);
pst.setString(1, "kzhdf");
pst.executeUpdate();