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 <schem_name>.clients — CoolBeans
– CoolBeans, Commented Apr 23, 2011 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
– Brian Roach, Commented Apr 23, 2011 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
– Szymon Lipiński, Commented Apr 23, 2011 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
– Matt, Commented Apr 24, 2011 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
– Szymon Lipiński, Commented Apr 24, 2011 at 20:01

user330315 · Accepted Answer · 2011-04-24 11:56:51Z

11

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\"";

answered Apr 24, 2011 at 11:56

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Kiddo Over a year ago

I'm trying to use sequelize ORM, and in its create query, it use quotes on the table_name. Thanks for the answer.

Gary · Accepted Answer · 2016-10-16 21:46:47Z

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, 2016 at 21:46

Gary

14k18 gold badges54 silver badges73 bronze badges

answered Aug 14, 2013 at 8:02

Richard Vidalo

1351 gold badge3 silver badges9 bronze badges

1 Comment

user330315 Over a year ago

The first, second and last statement refer to exactly the same table name.

Andrew Lazarus · Accepted Answer · 2011-04-23 20:19:28Z

1

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, 2011 at 20:19

Andrew Lazarus

19.5k3 gold badges38 silver badges57 bronze badges

1 Comment

Matt Over a year ago

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.

Winston Zvirikuzhe · Accepted Answer · 2018-07-06 07:56:25Z

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 6, 2018 at 7:56

answered Jul 6, 2018 at 7:42

Winston Zvirikuzhe

11 bronze badge

1 Comment

Nicolás Alarcón Rapela Over a year ago

should modify its response to adapt it to better syntax

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related