I recently took an EJB 3 application from MySQL to PostgreSQL. When I try to run the first part, I get an exception that looks like the following:

2010-09-08 15:16:48,658 ERROR [org.hibernate.util.JDBCExceptionReporter] (http-192.168.1.22-8080-1) Batch entry 0 insert into EJB3.DistributionCenter (addressLine1, addressLine2, city, description, state, zipCode, zipCodePlusFour, distributionCenterId) values ('10234 E. Side Street', NULL, 'Seattle', 'Washington Distribution Center', 'WA', '98101', '0', '10000') was aborted. Call getNextException to see the cause.
2010-09-08 15:16:48,658 WARN [org.hibernate.util.JDBCExceptionReporter] (http-192.168.1.22-8080-1) SQL Error: 0, SQLState: 3F000
2010-09-08 15:16:48,658 ERROR [org.hibernate.util.JDBCExceptionReporter] (http-192.168.1.22-8080-1) ERROR: schema "ejb3" does not exist

It doesn't think the EJB3 schema exists. Of course it does.

I have the following connection URL: <connection-url>jdbc:postgresql://localhost:5432/EJB3</connection-url>

I have the following in my persistence.xml:

<persistence-unit name="batch-services" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:/PostgresDS</jta-data-source>
<properties>
<property name="hibernate.hbm2ddl.auto" value="none"/>
<property name="hibernate.default_schema" value="EJB3"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="hibernate.order_updates" value="true"/>
<property name="hibernate.order_inserts" value="true"/>
<property name="hibernate.jdbc.batch_versioned_data" value="true"/>
<property name="hibernate.jdbc.fetch_size" value="50000"/>
<property name="hibernate.jdbc.batch_size" value="50000"/>
<property name="hibernate.default_batch_fetch_size" value="50000"/>
<property name="hibernate.connection.release_mode" value="auto"/>
<property name="hibernate.show_sql" value="true"/>
<!--
<property name="hibernate.generate_statistics" value="false"/>
-->
</properties>
</persistence-unit>

I created the database EJB3, with the schema called EJB3, as you can see from above I set the hibernate.default_schema property.

The generated SQL looks like the following:

2010-09-08 15:16:48,645 INFO [STDOUT] (http-192.168.1.22-8080-1) Hibernate: insert into EJB3.DistributionCenter (addressLine1, addressLine2, city, description, state, zipCode, zipCodePlusFour, distributionCenterId) values (?, ?, ?, ?, ?, ?, ?, ?)

That certainly looks correct. Having said that, when I use the graphical PGAdmin tool, and do some simply queries, I have to quote the schema and table names, like the following:

select * from "EJB3"."DistributionCenter"

If I don't quote them, then I get the same ejb3 schema doesn't exist error. Is there a problem with the PostgreSQL Dialect for PostgreSQL 8.4.4?

This is on EAP 5.0.1, and I know it wasn't tested with this newer version of PostgreSQL (tested with 8.2.4 and 8.3.7).

It doesn't appear that I have anything configured incorrectly, but maybe so.

Thanks to Tom Lane I was able to figure out what the problem was. It turns out that the PostgreSQL dialect does not generate proper SQL in the case where the schema and table and column names use mixed case. I had everything as mixed case for MySQL, which worked fine, but it does not work with PostgreSQL.

So, the workaround is to make everything lower case. I had to change the schema name, tables names and column names all to be lower case. Once I did that, everything is now working as expected.

It would be nice if the dialect supported mixed case, but at least in my case, its not essential.

Thanks to the above poster. I am using Netbeans 6.9.1 with hibernate and ran into the same issue.

The postgres dialect cannot handle mixed case schema/table names.

I can change everything in the db to lowercase but it would be nice if the dialect could handle mixed case.

I'm glad my experience could help. I was considering looking at the dialect code myself, to see if I could make it support mixed case. The other dialects I have used, like MySQL, Ingres, DB2, Oracle, etc., all seem to support mixed case table, schema names. I'm not sure why the PostgreSQL dialect doesn't. I can't be a difficult change to make.