Re: How to add explicit types so I can use OVERLAPS?

On Aug 1, 2008, at 4:07 PM, Federico Di Gregorio wrote:

> Il giorno ven, 01/08/2008 alle 14.47 +0000, Matthew Wilson ha scritto:
>> I want to use the OVERLAPS keyword to compare two time intervals, but
>> I'm having trouble.
>>
>> In the code below, I want to test if the time interval from 9:15 AM
>> through 9:45 AM overlaps the time interval from 8 AM through 10 AM.
>>
>> This approach fails:
>>
>>>>> d = {'9:15 AM':datetime(2008, 8, 1, 9, 15),
>>    ...      '9:45 AM':datetime(2008, 8, 1, 9, 45),
>>    ...      '8 AM':datetime(2008, 8, 1, 8),
>>    ...      '10 AM':datetime(2008, 8, 1, 10)}
>>
>>>>> cursor.execute('select (%(9:15 AM)s, %(9:45 AM)s) '
>>    ...                'overlaps (%(8 AM)s, %(10 AM)s);', d)
>>
>>    ------------------------------------------------------------
>>    Traceback (most recent call last):
>>      File "<ipython console>", line 1, in <module>
>>    ProgrammingError: function pg_catalog.overlaps(unknown, unknown,
>>    unknown, unknown) is not unique
>>    LINE 1: ...ct ('2008-08-01T09:15:00', '2008-08-01T09:45:00')  
>> overlaps
>>    (...
>>                                                                 ^
>>    HINT:  Could not choose a best candidate function. You might  
>> need to add
>>    explicit type casts.
>>
>> Meanwhile, this approach works fine:
>>
>>>>> cursor.execute('select (TIMESTAMP %(9:15 AM)s, TIMESTAMP %(9:45  
>>>>> AM)s) '
>>                       'overlaps (TIMESTAMP %(8 AM)s, TIMESTAMP %(10  
>> AM)s);', d)
>>
>>>>> cursor.fetchall()
>>    [(True,)]
>>
>> For a lot of boring reasons, I don't want to go into all my code  
>> and add
>> the TIMESTAMP cast.
>>
>> Is there anything else possible?
>
> This is PostgreSQL 8.3 not automatically casting anymore. We should
> probably change some of the getquoted() methods of the adapters to  
> make
> sure they add a (backend) typecast. And yes, patches are welcome.

I'd be opposed to a patch that made this the default behavior, because  
I have already some queries with things such as "timestamp %s" and so  
on. If psycopg added the "timestamp" bit for me I'd get syntax errors.  
Using CAST syntax might not cause a syntax error but could potentially  
change the semantics of my queries.

I think it would also run counter to the spirit of the SQL language. I  
think it's a frequently misunderstood fact that quoting something in  
SQL does not make it a string; it is a literal value of unknown type.  
The type can later be inferred (or coerced) as needed to match the  
type of other operands, which may be defined by tables, etc. It's nice  
to have the freedom to explicitly state the type of a literal or leave  
it up to coercion when writing a query.

That said, it's not hard to replace the adapter for datetime objects  
to insert the "timestamp" prior to the quoted value if that is more  
useful for your application. Please just don't do this for me.