Perl and DBI hashref keys case-sensitive
Perl and DBI are brilliant in giving you power to connect to a database and extract data. There is however something I never noticed, because I’m used to a PostgreSQL: when fetching a row as an hashref, the keys are stored in lowercase. This is not something tied to DBI, nor to Perl, rather to PostgreSQL and the way it handles SQL. In short, SQL is case insensitive, with PostgreSQL managing this as to lower case, while other databases manging this as to upper case. Now, clearly, if you don’t manage this, you can find yourself with the keys of an hash depending on the specific database:#!perl
use v5.40;
use DBD::Pg;
my $database = DBI->connect( 'dbi:Pg:dbname=testdb;host=rachel', 'luca', 'xxx' );
my $statement = $database->prepare( 'select * from perl' );
$statement->execute;
my $row = $statement->fetchrow_hashref;
say "Columns: ", join( ', ', keys $row->%* );
The above snippet reports the following output (assuming the
perl table have been created as follows):
% psql -h rachel -U luca -c '\d perl' testdb
Table "public.perl"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------
pk | integer | | not null | nextval('perl_pk_seq'::regclass)
a | text | | |
b | text | | |
Indexes:
"perl_pkey" PRIMARY KEY, btree (pk)
% perl test.pl
Columns: a, b, pk
However, the same piece of code can produce different results if the database engine is using uppercase everywhere (yeas Oracle, I’m looking at you!). I discovered that DBI provides a property, named
FetchHashKeyName, that can be set to either always lowercase with NAME_lc or viceversa, to always upper case with NAME_uc. This means you can overwrite the default behavior of your database with something like the following:
#!perl
use v5.40;
use DBD::Pg;
my $database = DBI->connect( 'dbi:Pg:dbname=testdb;host=rachel', 'luca', 'xxx' );
$database->{ FetchHashKeyName } = 'NAME_uc';
my $statement = $database->prepare( 'select * from perl' );
$statement->execute;
my $row = $statement->fetchrow_hashref;
say "Columns: ", join( ', ', keys $row->%* );
That produces as output
Columns: A, B, PK.
Please note that you need to setup the case before the statement is prepared.
Now, when is this useful? Clearly, when you are using Perl to migrate some data from a different database, like I was doing when I discovered this feature.