[Pdx-pm] Postgres and DBI issue

[Chris Dawson]

Hi there,

I'm running a query which fails inside of DBI, but works without issue
from the psql client.  Does anyone have suggestions?  This is not a
complicated query, and I am unclear as to why the perl version
requires a cast of some sort.

Here are the queries, followed by the error ("DBD::Pg::db
selectall_arrayref failed: ERROR:  operator is not unique: time
without time zone + time with out time zone HINT:  Could not choose a
best candidate operator. You may need to add explicit type casts.")

my $single_query = <<"END";
select * from schedule where
( recurring is null or recurring = '' )
and
( start_date + start_time ) < now()
and
( end_date + end_time ) > now ();
END

my $recurring_query = <<"END";
select * from schedule where
to_char( now(), 'Dy' ) = recurring
and
( now() + start_time ) < now()
and
( now() + end_time ) > now ();
END

foreach my $query ( $recurring_query, $single_query ) {
    &_log( "Query: $query" ) if $verbose;
    my $results = $dbh->selectall_arrayref( $query );
    foreach my $item ( @{$results} ) {

ERROR:

root at piab:~/podcast-core/rails/ssl/db#
/opt/wiab/custom/bin/automate_encoders --verbose
Verbose is on
LOG: Query: select * from schedule where
to_char( now(), 'Dy' ) = recurring
and
( now() + start_time ) < now()
and
( now() + end_time ) > now ();
LOG: Query: select * from schedule where
( recurring is null or recurring = '' )
and
( start_date + start_time ) < now()
and
( end_date + end_time ) > now ();
DBD::Pg::db selectall_arrayref failed: ERROR:  operator is not unique:
time without time zone + time with
out time zone
HINT:  Could not choose a best candidate operator. You may need to add
explicit type casts.

I've tried with both ->prepare() and ->selectall_arrayref()

Within the Psql client it returns no entries, but it does not die with an error.

wiab_dev=# select * from schedule where to_char( now(), 'Dy' ) =
recurring and ( now() + start_time ) < now() and ( now() + end_time )
> now();
 uniqid | name | description | start_time | status | archive |
automate | recurring | category | remote_source | comment |
remote_archive_protocol | remote_archive_path |
remote_archive_username | remote_archive_password |
remote_archive_host | end_time | end_date | start_date
--------+------+-------------+------------+--------+---------+----------+-----------+----------+---------------+---------+-------------------------+---------------------+-------------------------+-------------------------+---------------------+----------+----------+------------
(0 rows)

wiab_dev=# select * from schedule where ( recurring is null or
recurring = '' ) and ( start_date + start_time ) < now() and (
end_date + end_time ) > now ();
 uniqid | name | description | start_time | status | archive |
automate | recurring | category | remote_source | comment |
remote_archive_protocol | remote_archive_path |
remote_archive_username | remote_archive_password |
remote_archive_host | end_time | end_date | start_date
--------+------+-------------+------------+--------+---------+----------+-----------+----------+---------------+---------+-------------------------+---------------------+-------------------------+-------------------------+---------------------+----------+----------+------------
(0 rows)

wiab_dev=# \d schedule;
                                           Table "public.schedule"
         Column          |          Type          |
     Modifiers
-------------------------+------------------------+-----------------------------------------------------------
 uniqid                  | integer                | not null default
nextval('schedule_uniqid_seq'::regclass)
 name                    | character varying(255) |
 description             | character varying(255) |
 start_time              | time without time zone |
 status                  | character varying(255) |
 archive                 | character varying(255) |
 automate                | character varying(255) |
 recurring               | character varying(255) |
 category                | character varying(255) |
 remote_source           | text                   |
 comment                 | character varying(255) |
 remote_archive_protocol | character varying(255) |
 remote_archive_path     | character varying(255) |
 remote_archive_username | character varying(255) |
 remote_archive_password | character varying(255) |
 remote_archive_host     | character varying(255) |
 end_time                | time without time zone |
 end_date                | date                   |
 start_date              | date                   |
Indexes:
    "schedule_pkey" PRIMARY KEY, btree (uniqid)


Thanks,
Chris