V_CATALOG.TABLES.TABLE_NAME column is cAsE SeNsiTiVe!

[Josh]

Hi,

This was driving me crazy! I couldn't find a table a user created in the database in the TABLES system table, that is, until I realized that the TABLE_NAME column is case sensitive! So, just as an FYI to other folks:

Code: Select all

dbadmin=> create table Test (aB int);
CREATE TABLE
dbadmin=> select table_name from tables where table_name = 'test';
 table_name
------------
(0 rows)

Won't work:

Code: Select all

dbadmin=> select table_name from tables where table_name = 'test';
 table_name
------------
(0 rows)

Does work:

Code: Select all

dbadmin=> select table_name from tables where table_name = 'Test';
 table_name
------------
 Test
(1 row)

Won't work:

Code: Select all

dbadmin=> select column_name from columns where column_name = 'ab';
 column_name
-------------
(0 rows)

Does work:

Code: Select all

dbadmin=> select column_name from columns where column_name = 'aB';
 column_name
-------------
 aB
(1 row)

But the \d meta command isn't case sensitive!

Code: Select all

dbadmin=> \d test;
                                   List of Fields by Tables
    Schema    | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------------+-------+--------+------+------+---------+----------+-------------+-------------
 intersect_wh | Test  | aB     | int  |    8 |         | f        | f           |
(1 row)

dbadmin=> \d Test;
                                   List of Fields by Tables
    Schema    | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------------+-------+--------+------+------+---------+----------+-------------+-------------
 intersect_wh | Test  | aB     | int  |    8 |         | f        | f           |
(1 row)

Annoying!

[id10t]

Hi!

Don't panic!

Read this: Identifiers
(read note: Special note about Case-sensitive System Tables at the end of papper)