2

I am developing a java web service that is deployed in wildly. It is connected to a postgresql database. In this database, I have a table called xx_activity. In it there is a column called "id", which is also the primary key. Here is the query used to create the table:

CREATE TABLE xx_activity
(
  id serial NOT NULL,
  baseitemid integer
);

to connect to this table, I use the following java code:

conn = postgresVoyateDBConnection();
            query = conn.prepareStatement("select id, baseitemid" +
                                            "from xx_activity " +
                                            "where \"id\" = ? ");
            
            query.setInt(1, id);
            ResultSet rs = query.executeQuery();

However, when I call the method that includes this code, I get an error:

org.postgresql.util.PSQLException: ERROR: column "id" does not exist

Position: 8

This is confusing because I certainly have this column. i added escape characters as per this answer, but it did not solve the issue.

Also note that queries without the where clause, like:

conn = postgresVoyateDBConnection();
        query = conn.prepareStatement("select id, baseitemid " +
                                        "from xx_activity");
        
        ResultSet rs = query.executeQuery();

work perfectly.

I have also tried without using escape characters but it gives the same error. I also checked in pgadmin and there is no trailing space in the column name, neither are there any upper case letters involved (in which case, the other select query shouldn't have worked?).

How can this be fixed?

Community
  • 1
  • 1
toing_toing
  • 2,334
  • 1
  • 37
  • 79
  • 1
    Use pgAdmin or some other tool to connect to your database using same credentials as you use in your app. Execute `select * from xx_activity` Is there id column in the result? Check if you have two `xx_activity` tables- one in public schema, other in user schema. – tata.leona Sep 09 '16 at 09:13
  • @112g there is only 1 schema, the public schema. i tried a a select and it works, even from the java code. Also, even the where clause works from pgadmin, wondering why it doesn't work from the code? – toing_toing Sep 09 '16 at 09:22

1 Answers1

2

Fixed this, the issue was a missing space. After the first line of the query, there needs to be a space as belows:

query = conn.prepareStatement("select id, baseitemid " +
                                            "from xx_activity " +
                                            "where \"id\" = ? ");

EDIT: escape charactors not needed for id; so final answer should be:

 query = conn.prepareStatement("select id, baseitemid " +
                                                "from xx_activity " +
                                                "where id = ? ");
toing_toing
  • 2,334
  • 1
  • 37
  • 79