Accessing a PostgreSQL table using Hibernate

I was goofing around with PostgreSQL, and I wanted to give it a try, so I decided to replicate my Payment table from HSQLDB to PostgreSQL and I got something like this

CREATE TABLE "PAYMENTS"
(
  id_order integer NOT NULL,
  id_customer integer NOT NULL,
  total_amount_transaction double precision,
  transaction_number character varying(50),
  creation_ts timestamp with time zone,
  CONSTRAINT pk_payments PRIMARY KEY (id_order, id_customer)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "PAYMENTS" OWNER TO payments;

As you well know with Hibernate the switch should be not very painful, and it won't have any impact on existing code!
But...Surprise!

The impact is not a huge impact but it's something really specific, and that's why I'm creating this post.

The usual steps to chnage from one DB to another DB is Hibernate is really simple:
This is my previous DataSource - Because, to accelerate the process I did the change locally before deploying this to my app server, so I can test locally using my existing JUnit test cases <bean id="hSqlDataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="org.hsqldb.jdbcDriver"/> <property name="url" value="jdbc:hsqldb:hsql://localhost/"/> <property name="username" value="SA"/> <property name="password" value=""/> <property name="defaultAutoCommit" value="false"/> </bean>
And this is my new DataSource for PostgreSQL
Important: Remove the "defaultAutoCommit" attribute and add "defaultCatalog" attribute <bean id="postgreSqlDataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="org.postgresql.Driver"/> <property name="url" value="jdbc:postgresql://localhost:5432/little-store-payments"/> <property name="username" value="payments"/> <property name="password" value="1234"/> <property name="defaultCatalog" value="public"/> </bean>
This is my new Dialect <prop key="hibernate.dialect"> <!-- org.hibernate.dialect.HSQLDialect --> org.hibernate.dialect.PostgreSQLDialect </prop>

And this is the second Important detail:
Add double quotes to your table name!, just like this @Entity @Table(name = "\"PAYMENTS\"") public class Payment implements Serializable { private static final long serialVersionUID = 29461940044295039L; @EmbeddedId private PaymentId id; @Column(name = "total_amount_transaction", nullable = false, precision = 6, scale = 2) private Float totalAmountTransaction; @Column(name = "transaction_number", nullable = false, updatable = false, unique = true, length=50) private String transactionNumber; @Temporal(TemporalType.TIMESTAMP) @Column(name = "creation_ts", length = 19, updatable = false, insertable = true) private Date creationTs; ... }
My original code used to be

@Table(name = "PAYMENTS")

@Embeddable public class PaymentId implements Serializable { private static final long serialVersionUID = 4176341157636925851L; @Column(name = "id_order", nullable = false, updatable = false) private Integer idOrder; @Column(name = "id_customer", nullable = false, updatable = false) private Integer idCustomer; ... }

This is my new Payments DB :)

Hibernate did't again!