upper, lower and mixed case

September 24, 2012 — Leave a comment

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.

Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s