The column name ... was not found in this ResultSet

Question

We are using java jdk 1.7.0_45, postgresql jdbc connector postgresql-9.3-1100.jdbc41.jar.

Here is a synopsis of our problem, as much as possible of code pasted below.

This code:

        ResultSet rs = DbConn.getInstance().doQuery("Select d.deptId from Depts d");
                while (rs.next()){
                    System.out.println(rs.getInt("d.deptId"));

Produces the error:

    org.postgresql.util.PSQLException: The column name d.deptId was not found in this ResultSet.

This code:

                        ResultSet rs = DbConn.getInstance().doQuery("Select d.deptId from Depts d");
                while (rs.next()){
                    System.out.println(rs.getInt("deptId"));

Produces no error.

Is there a way, besides removing the "d." from the first query, to make the first code snippet not throw the error message?

Here is the source code:

public class JoinTest {
    @Test
    public void test(){
        boolean pass = false;
        try {
            ResultSet rs = DbConn.getInstance().doQuery("Select d.deptId from Depts d");
            String label = rs.getMetaData().getColumnLabel(1);  // What do you get?
            System.out.println("label = " + label);
            while (rs.next()){
                System.out.println(rs.getInt("d.deptId"));
                pass = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            pass=false;
        }
        assertTrue(pass);
    }
    @Test
    public void test2(){
        boolean pass = false;
        try {
            ResultSet rs = DbConn.getInstance().doQuery("Select d.deptId from Depts d");
            while (rs.next()){
                System.out.println(rs.getInt("deptId"));
                pass = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            pass=false;
        }
        assertTrue(pass);
    }
}

    public class DbConn {


    private static String url = "jdbc:postgresql://server:port/schema";
        private static Properties props = new Properties(); {
            props.setProperty("user","userid");
            props.setProperty("password","passwprd");
        }
        private  Connection conn;

        private DbConn(){}
        private static DbConn instance;
        public static DbConn getInstance() throws SQLException{
            if (instance == null){
                instance = new DbConn();
                instance.conn = DriverManager.getConnection(url, props);
            }
            return instance;
        }
        public ResultSet doQuery(String query) throws SQLException{
            Logger.log("DbConn.doQuery: " + query);
            Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(query);
                return rs;
        }
        }

}

Craig Ringer Craig Ringer · Accepted Answer · 2014-01-22T00:30:36

The query:

 select d.deptId from Depts d

produces a single-column resultset with the result-alias "deptId". There is no "d.deptId" column. If you want one, you can request that as the column alias instead:

 select d.deptId AS "d.deptId" from Depts d

PgJDBC can't do anything about this because it has no idea that the resultset column "deptId" is related to the "d.deptId" in the select-list. Teaching it about that would force it to understand way more about the SQL it processes than would be desirable, and lead to maintenance and performance challenges.

The column name ... was not found in this ResultSet

3 Answers