Re: DBI, Postgresql, and the SQL_DATE type

[Collin Peters]

Bump
Does anybody have any ideas on this one?  It seems like a pretty serious 
issue.

Collin
Collin Peters wrote:
I am running a postgresql database and am finding that DBI is returning 
the wrong data type for the postgresql DATE type.  Here is some example 
code:

====================================
<snip - connection info/>
my $sql = "SELECT birthdate, points, username FROM _users WHERE user_id 
= 27";
my $sth = $dbh->prepare("$sql");
$sth->execute;

my $columnTypes = $sth->{TYPE};
print Dumper($columnTypes);
while (my @row = $sth->fetchrow_array) {
    my ($birthdate, $points, $username) = @row;
    print "BIRTHDATE: $birthdate\n";
    print "POINTS: $points\n";
    print "USERNAME: $username\n";
}
$sth->finish;
print "\n\nBIRTHDATE COLUMN INFO\n";
my $sth = $dbh->column_info(undef, undef, 'pp_users', 'birthdate');
$sth->execute;
while($_ = $sth->fetchrow_hashref()) {
    while(my ($key,$value) = each(%$_)) {
        print "$key: $value\n" if(defined($value));
    }
    print "-------------\n";
}
$sth->finish;
$dbh->disconnect;
====================================
 The output of this script is:
$VAR1 = [
          0,
          8,
          12
        ];
BIRTHDATE: 1958-12-05
POINTS: 3228
USERNAME: jim
BIRTHDATE COLUMN INFO
pg_type_only: date
TABLE_SCHEM: public
IS_NULLABLE: YES
COLUMN_SIZE: 4
ORDINAL_POSITION: 9
COLUMN_NAME: birthdate
TYPE_NAME: date
pg_type: date
NULLABLE: 1
DATA_TYPE: 0
TABLE_NAME: pp_users
The problem lies with the birthdate column.  In postgresql, the data 
type is 'date'.  When I run the above code it returns the DATA_TYPE as 
0, which is DBI::SQL_ALL_TYPES, not 9 which is DBI:SQL_DATE.

So my questions are:
1) Is this DBI's problem or Postgresql's problem?
2) Why is it not returning DBI::SQL_DATE as the type for postgresql date 
fields?

I haven't been able to find this issue mentioned anywhere in any google 
searches.

Regards,
Collin