CODEWITHSUNDEEP
javasqlpostgresqljdbc
Landister
Landister

Reputation: 2224

JDBC capitalizing the table in my postgres query

I am running the following code 

/**
 * @param args
 */
public static void main(String[] args) throws SQLException {
    System.out.println("starting");

    org.postgresql.Driver driver = new org.postgresql.Driver();
    DriverManager.registerDriver(driver);

    Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/epcfe/", "postgres", "aap123!");

    Statement st = con.createStatement();
    st.executeQuery("select * from epcfeschema.PRODUCT");

    System.out.println("done");
}

I keep getting Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "epcfeschema.product" does not exist

If I create a table with the lowercase name product this works fine but I need it to work for tables with all caps. How do I keep JDBC from lowercasing my table?

Upvotes: 1

Views: 10086

Answers (4)

Bohemian
Bohemian

Reputation: 425013

If it's a hibernate issue, try this:

@Entity
@Table(name = "\"PRODUCT\"")
public class Product { // ...

Or better yet, make your life easy: log on to postgres and rename the table!

ALTER TABLE "PRODUCT" rename to product;

(Of course, other code may depend on the cap name...)

Upvotes: 6

user330315
user330315

Reputation:

You most probably created your tables using double quotes, e.g: 

create table "PRODUCTS" (
  ...
)

This makes the table name case-senstive (as per the SQL Standard), and thus you need to use double quotes each time you acces the table.

select * from epcfeschema."PRODUCT"

and therefor you must use:

executeQuery("select * from epcfeschema.\"PRODUCT\"");

in your Java code (as ChssPly76 has also shown).

I would strongly recommend to re-create the tables without double quotes to make them case-insensitive. Then you never need them and you never have to worry about writing them im UPPER, lower or MixedCase:

When using

create table PRODUCTS (
  ...
)

all of the following statements will work:

select * from Products;
select * from PRODUCTS;
select * from products;

So you can write all of your table names in upppercase if you want.

Upvotes: 6

Dmitri
Dmitri

Reputation: 9157

Postgres (and any other reasonable database) table names are case insensitive (see here). Your problem is somewhere else - permissions, maybe?

Upvotes: 2

ChssPly76
ChssPly76

Reputation: 100706

Quote your table names:

st.executeQuery("select * from epcfeschema.\"PRODUCT\"");

Upvotes: 2

Related Questions