Search code examples
postgresqlopenjpa

postgres schema not found when create with upper case

I am trying to create app using OpenJPA & Postgres 9.2.xx, Currently facing issue at DB level

1) Created schema say PCM:-

CREATE SCHEMA PCM

2) Tried create table :-

CREATE TABLE PCM.USER_PROFILE (
USER_PROFILE_ID BIGINT NOT NULL,
USER_FNAME VARCHAR(60),
USER_LNAME VARCHAR(60)
};

Got error "pcm" schema does not exists

Then tried creating table :-

CREATE TABLE "PCM.USER_PROFILE" (
USER_PROFILE_ID BIGINT NOT NULL,
USER_FNAME VARCHAR(60),
USER_LNAME VARCHAR(60)
};

Table is created successful, If I list the schema:-

[postgres@DBMigration ~] $ psql -c "\dn"

  List of schemas
  Name  |  Owner
--------+----------
 pcm    | dbadmin
 public | postgres

B) In persistence.xml , I have entered configuration 

<property name="openjpa.jdbc.Schema" value="PCM" />

Now I am getting issue in OpenJPA stating schema is not present.

I tried refering here, but no success.

I have tried entering schema name in configuration as '\"PCM\"', "\"PCM\"", '\"pcm\"', "\"pcm\"".

Not sure where am I going wrong.

I need suggestion/help, 1) how or what is proper standard to create schema in Postgres & refer while creating table.

2) Is my entry in persistence.xml correct? Then why its not identifying the schema

Solution

  • Object names in Postgres when not quoted are implicitly converted to lower case.

    When you create a table the way you did below with quotation mark on "PCM.USER_PROFILE" then the table is created in default public schema with the name of "PCM.USER_PROFILE".

    CREATE TABLE "PCM.USER_PROFILE" (
  USER_PROFILE_ID BIGINT NOT NULL,
  USER_FNAME VARCHAR(60),
  USER_LNAME VARCHAR(60)
);

    However, your create statement mentioned in the post is completely valid (with the exception of changing } to ) at the end of command:

    CREATE TABLE PCM.USER_PROFILE (
  USER_PROFILE_ID BIGINT NOT NULL,
  USER_FNAME VARCHAR(60),
  USER_LNAME VARCHAR(60)
);

    It creates user_profile table under pcm schema succesfully.