Blog de Pierrick Le Gall

db1=> create table blog_ticket (Id int, Title character varying(50));
CREATE TABLE
db1=> \d blog_ticket
           Table «public.blog_ticket»
 Column  |         Type          |
---------+-----------------------+
 id      | integer               |
 title   | character varying(50) |

As you can see, my capitalized column names have been lowercased. This is not a real problem as long as you never try to have two column names differentiated only by case.

If you really want to have ID and Id in the same table, you have to double quote column names.

db1=> drop table blog_ticket;
DROP TABLE
db1=> create table blog_ticket ("Id" int, "ID" int, Title character varying(50));
CREATE TABLE
db1=> \d blog_ticket
           Table «public.blog_ticket»
 Column  |         Type          |
---------+-----------------------+
 Id      | integer               |
 ID      | integer               |
 title   | character varying(50) |

Even with a good reason, don't do that, please.

Now I want to retrieve all the tables of a given schema with a Perl script. The equivalent of :

db1=> \dt public.*
             Relation list
 Schema |    Name     | Type  | Owner
--------+-------------+-------+-------
 public | Blog_Ticket | table | root
 public | blog_ticket | table | root
(2 lines)

Here is the Perl script, I name it list_schema_tables.pl:

#!/usr/bin/perl
 
use strict;
use warnings;
 
use Getopt::Long;
use DBI;
 
my %opt = ();
GetOptions(
    \%opt,
    qw/dbname=s dbuser=s dbpass=s dbhost=s dbport=i schema=s/
);
 
foreach my $option (qw/dbname dbuser dbpass/) {
    if (!exists($opt{$option})) {
        die 'Error: '.$option.' is a mandatory option'."\n";
    }
}
 
$opt{schema} ||= 'public';
$opt{dbhost} ||= 'localhost';
$opt{dbport} ||= 5432;
 
my $dbh = DBI->connect(
    sprintf(
        'DBI:Pg:dbname=%s;host=%s;port=%s;',
        $opt{dbname},
        $opt{dbhost},
        $opt{dbport},
    ),
    $opt{dbuser},
    $opt{dbpass},
)
    or die "can't connect to database";
 
my $sth = $dbh->table_info('%', $opt{schema});
my @tables = ();
 
while (my $entity = $sth->fetchrow_hashref()) {
    if ($entity->{TABLE_TYPE} eq 'TABLE') {
        push @tables, $entity->{TABLE_NAME};
    }
}
 
print(
    join(
        "\n",
        @tables
    ),
    "\n"
);

This script, is used in the following way:

$ ./list_schema_tables.pl --dbn=talend --dbu=root --dbpass=toor --schema=public
blog_ticket
"Blog_Ticket"

What we see here is that table names mixing lower and upper case characters are double quoted. Very... unpredictable, isn't it? The funniest is that in the table_info function, I mustn't surround my schema with double quotes, even if it contains uppercased characters...

As a conclusion, I would simply say: don't ever use explicit case mixing in column/table/schema names, whatever the database management system you use.