Wednesday, June 5, 2013

Relation does not exist, but can access if i'm using within double quote

SQL is not case sensitive as long as you omit the quotes. The following names are all identical: 

           EMPLOYEE, EmPLoyee, emploYEE

The following tables are different: 

           "EMPLOYEE", "EmPLoyee", "emploYEE"

while using this 

\d EMPLOYEE (or)  \d employee

   it throw error : relation EMPLOYEE does not  exist.


   it display the EMPLOYEE relation description 

if you get struck some where in PostgreSQL like this, do rename the table to either all upper/lower case.

1) \o /tmp/rename_tbl_lower

2) select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' ||
lower(tablename)||';' from pg_tables where schemaname = 'public';

come out of psql then run

3) psql -U username database < /tmp/rename_tbl_lower

enter into psql 

\d employee 

 it display the EMPLOYEE relation description  

I've faced this issue while migrating data from Oracle to PostgreSQL  using Navigator tool. normally navigator tool ignore the constraint. it focus only the data migration part.  for this reason i just used ispirer-sqlways for convertion of  schema.

relation name created by  navigator and ispirer-sqlways are totally Ir-relevant, so I renamed all relation by upper/lower as said above.

No comments: