upper, lower and mixed case

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.