Java SQL "ERROR: Relation "Table_Name" does not exist"

Question

I'm trying to connect netbeans to my postgresql database. The connection seems to have worked as I don't get any errors or exceptions when just connecting, methods such as getCatalog() also return the correct answers.

But when I try to run a simple SQL statement I get the error "ERROR: relation "TABLE_NAME" does not exist", where TABLE_NAME is any one of my tables which DO exist in the database. Here's my code:

    Statement stmt = con.createStatement();

    ResultSet rs;

    String query = "SELECT * FROM clients";

    rs = stmt.executeQuery(query);

I was thinking that netbeans might not be finding the tables because it's not looking in the default schema (public), is there a way of setting the schema in java?

EDIT: My connection code. The database name is Cinemax, when I leave out the statement code, I get no errors.

    String url = "jdbc:postgresql://localhost:5432/Cinemax";
    try{

    try {
        Class.forName("org.postgresql.Driver");
    } catch (ClassNotFoundException cnfe) {
        System.err.println("Couldn't find driver class:");
        cnfe.printStackTrace();
    }

    Connection con = DriverManager.getConnection( url,"postgres","desertrose147");

Can't you re-write the sql this way? `SELECT * FROM .clients` — CoolBeans, Apr 23 '11 at 18:37
You aren't showing how you connect to the database server. I suspect @CoolBeans is correct above or very close. Your table is in a different schema (which the above will fix) or in another database than the one you specify when connecting. — Brian Roach, Apr 23 '11 at 20:14
I love that... could you show us the REAL error you have? I don't think that the database is saying "relation TABLE_NAME..." when you execute "select * from clients". — Szymon Lipiński, Apr 23 '11 at 20:20
I tried that but I get the same error, "ERROR: relation "public.clients" does not exist" (same for any other one of my tables). public is my only schema, so it's also the default schema. Thanks for the help so far. — Matt, Apr 24 '11 at 11:12
Set log_min_duration_statement to 0 in postgresql.conf, restart the database, run the application and check in the postgresql logs what is the real query that is sent to the database. And one more thing... are you 100% sure that you have the table there? Can you connect to this database using psql/pgadmin and execute the query there? — Szymon Lipiński, Apr 24 '11 at 20:01

score 11 · Accepted Answer · answered Apr 24 '11 at 11:56

I suspect you created the table using double quotes using e.g. "Clients" or some other combination of upper/lowercase characters and therefor the table name is case sensitive now.

What does the statement

 SELECT table_schema, table_name
 FROM information_schema.tables 
 WHERE lower(table_name) = 'clients'

return?

If the table name that is returned is not lowercase you have to use double quotes when referring to it, something like this:

String query = "SELECT * FROM \"Clients\"";

I'm trying to use sequelize ORM, and in its create query, it use quotes on the table_name. Thanks for the answer. — Kiddo, Jul 29 '13 at 07:59

score 2 · Answer 2 · edited Oct 16 '16 at 21:46

2

You could check these possibilities:

String query = "SELECT * FROM clients";
String query = "SELECT * FROM CLIENTS";
String query = "SELECT * FROM \"clients\"";
String query = "SELECT * FROM \"CLIENTS\"";
String query = "SELECT * FROM Clients";

Maybe one of those would work.

edited Oct 16 '16 at 21:46

Gary

13,303
18
49
71

answered Aug 14 '13 at 08:02

Richard Vidalo

135
1
3
9

The first, second and last statement refer to exactly the same table name. – Aug 14 '13 at 09:39

Winston Zvirikuzhe · Answer 3 · 2018-07-06T07:56:25.977

0

Funny thing is i was experiencing the same thing as i had just started on netbeans and postgressql db, and the error was fixed after noting that the issue was that my tables in postgressql had capital letters in my naming convention which me and my jdbc query statement for INSERT was failing to find the table. But after renaming my tables in the db and fixing the column names as well am good to go. Hope it helps.

edited Jul 06 '18 at 07:56

answered Jul 06 '18 at 07:42

Winston Zvirikuzhe

1
1

should modify its response to adapt it to better syntax – Nicolás Alarcón Rapela Jul 06 '18 at 07:51

score 0 · Answer 4 · answered Apr 23 '11 at 20:19

0

Besides CoolBeans' suggestion, you may also be connecting to the db as a different user who does not have permission on the relevant db or schema. Can you show the connection string?

answered Apr 23 '11 at 20:19

Andrew Lazarus

18,205
3
35
53

I'm the only database user (postgres/root user), since this is just a small database which I'm using to learn postgresql. I included the connection string in an edit in my original question. Thanks for the help so far. – Matt Apr 24 '11 at 11:14

Java SQL "ERROR: Relation "Table_Name" does not exist"

4 Answers4

Linked

Related