while learning more about postgresql I came across the default case in postgresql ( it is lower case ). so, when querying the dictionary/catalog you’ll have to provide the lower case names to get any results:
postgres=# create table test1 ( a numeric ); CREATE TABLE postgres=# select relname from pg_class where relname = 'TEST1'; relname --------- (0 rows) postgres=# select relname from pg_class where relname = 'test1'; relname --------- test1 (1 row)
in oracle you’ll need to use upper case by default:
SQL> create table test1 ( a number ); Table created. SQL> select table_name from dba_tables where table_name = 'TEST1'; TABLE_NAME ------------------------------ TEST1 SQL> select table_name from dba_tables where table_name = 'test1'; no rows selected SQL>
if you want postgresql to respect the case when creating objects you’ll need to put double quotes around the names:
postgres=# create table "TEST2" ( a numeric ); CREATE TABLE postgres=# select relname from pg_class where relname = 'TEST2'; relname --------- TEST2 (1 row) postgres=# select relname from pg_class where relname = 'test22'; relname --------- (0 rows)
same in oracle:
SQL> create table "test2" ( a number ); Table created. SQL> select table_name from dba_tables where table_name = 'test2'; TABLE_NAME ------------------------------ test2 SQL> select table_name from dba_tables where table_name = 'TEST2'; no rows selected
knowing this, is it possible to create identical tables which just differ in the case of their name ? :
postgresql:
postgres=# create table test3 ( a numeric ); CREATE TABLE postgres=# create table "Test3" ( a numeric ); CREATE TABLE postgres=# create table "TesT3" ( a numeric ); CREATE TABLE postgres=# select relname from pg_class where upper(relname) like 'TEST3%'; relname --------- Test3 TesT3 test3 (3 rows)
not an issue with postgresql. what about oracle ?
SQL> create table test3 ( a number ); Table created. SQL> create table "Test3" ( a number ); Table created. SQL> create table "TesT3" ( a number ); Table created. SQL> select table_name from dba_tables where upper(table_name) like 'TEST3%'; TABLE_NAME ------------------------------ TesT3 Test3 TEST3
same behaviour. If someone had asked me if this is possible in oracle before, I would have said: no, definitely not. lessons learned ? :)
going further: what about constraint names ?
in postgresql:
postgres=# alter table test3 add constraint c1 check ( a is not null ); ALTER TABLE postgres=# alter table test3 add constraint "C1" check ( a > 5 ); ALTER TABLE postgres=# select conname,consrc from pg_constraint where upper(conname) = 'C1'; conname | consrc ---------+-------------------- c1 | (a IS NOT NULL) C1 | (a > (5)::numeric)
ok, this is consistent. what about oracle ? :
SQL> alter table test3 add constraint c1 check ( a is not null );
Table altered.
SQL> alter table test3 add constraint "C1" check ( a > 5 );
alter table test3 add constraint "C1" check ( a > 5 )
*
ERROR at line 1:
ORA-02264: name already used by an existing constraint
what about indexes ? postgresql:
postgres=# create index i1 on test3(a); CREATE INDEX postgres=# create index "i1" on test3(a); ERROR: relation "i1" already exists postgres=# create index "I1" on test3(a); CREATE INDEX postgres=# select indexname,indexdef from pg_indexes where upper(indexname) = 'I1'; indexname | indexdef -----------+-------------------------------------------- i1 | CREATE INDEX i1 ON test3 USING btree (a) I1 | CREATE INDEX "I1" ON test3 USING btree (a) (2 rows)
oracle:
SQL> create index i1 on test3 ( a );
Index created.
SQL> create index "i1" on test3 ( a );
create index "i1" on test3 ( a )
*
ERROR at line 1:
ORA-01408: such column list already indexed
as oracle checks if an index is defined on the same column(s) this is not possible. slightly modified test:
SQL> alter table test3 add ( b number );
Table altered.
SQL> create index "I1" on test3 ( b );
create index "I1" on test3 ( b )
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
still not possible.
I did not check all the objects but it seems that oracle is not as consistent as postgresql in this case.
Daniel Westermann's Blog 