Psycopg psycopg

Comments and changes to this ticket

• 

  Daniele Varrazzo April 3rd, 2011 @ 11:06 PM

  • State changed from “new” to “invalid”

  PostgreSQL is now (since before 2.4) more strict about types. You are passing a Python date to psycopg, so psycopg is passing a date to postgres. There is no function to_timestamp(date, whatever).

  From the traceback you have sent it's hard to understand the context where the date is used. If it's just used for the comparison in the WHERE you don't need to parse the timestamp on the server: the call to_timestamp() can be dropped at all from your code. If you have to do some timestamp arithmetic in the query and the value is just a date, then use a cast such as %s::timestamp.

  This is not a bug. If you need help please write to the mailing list.

• You flagged this item as spam.
  

  Mark Lakewood April 4th, 2011 @ 01:28 AM

  Hi Daniele,

  Sorry, I should've put the SQL in to make it easier

  SELECT COUNT(timestamp), album_artist, track_name, music_playlist.name
  

          FROM music_song_played 
          JOIN music_song ON music_song_played.song_played_id = music_song.id 
          JOIN music_user_listened ON music_song_played.playlist_from_id = music_user_listened.playlist_id 
          JOIN auth_user ON music_user_listened.user_id = auth_user.id 
          JOIN music_playlist ON music_user_listened.playlist_id = music_playlist.id 
          WHERE (music_song_played.timestamp > to_timestamp(%s, 'YYYY-MM-DD') AND 
                 music_song_played.timestamp < to_timestamp(%s, 'YYYY-MM-DD') ) AND 
                 music_song_played.timestamp > music_user_listened.start  AND 
                music_song_played.timestamp < music_user_listened.end 
          GROUP BY timestamp, album_artist, track_name, music_playlist.name;"
  

  what im trying to do is cast a Date to a timestamp so it can be compared in the where clause to a timestamp. The reason I think this is a bug is that the SQL works in postgres 8.4. ie if I plug this into it 8.4 it will work. I understand if postgreSQL has become more strict about types. But If I can execute this sql against my postgres database, surely something is wrong here?

  I'll have a look at the %s::timestamp to see if that fixes my problem.

  Cheers

  Mark

• You flagged this item as spam.
  

  Mark Lakewood April 5th, 2011 @ 01:49 AM

  Hi Daniele,

  The %s::timestamp does work, so im fine.

  I guess its just a bit strange for postgresql to accept the sql but for psycopg2 to not accept it. Might be something that needs to be looked into?

  Cheers

  Mark

• 

  Daniele Varrazzo April 5th, 2011 @ 07:46 AM

  No the behavior is perfectly known and we consider it correct. When you pass a date object to psycopg it creates a postgres date, not an unknown literal, by adding a cast. This behavior changed somewhere around psycopg 2.2, and has broken your program because it was doing something not correct: it is passing a python date to a postgres function taking a string argument.

  >>> cur.execute("select %s as mydate", (date(2011,04,05),))
  
  >>> cur.query  # psycopg has added a cast
  "select '2011-04-05'::date as mydate"
  
  >>> cur.fetchone()  # the type is "strong" and we have been able
                      # to make a complete roundtrip with it
  (datetime.date(2011, 4, 5),)
  

  The cast we added helps postgres to disambiguate the type when it's used in an ambiguous context such as argument of an overloaded function, which is exactly the reason your original program may have needed the parse_date kludge. I believe it was needed somewhere else, not in the query you sent, where an "unknown" type would have worked as well.

  Also note that I'm pretty sure you don't need a cast at all for the query you have written above: the cast from date to timestamp is implicit, which means Postgres will do it by itself. The cast may have been used instead of parse_date with older psycopg versions too (where the ::date wasn't appended) and it would have been a better solution, as the query parser would have known the values at planning time, whereas the function call hides the value and may force the selection of a suboptimal plan.

  Using to_date to parse psycopg arguments is just wrong: it is unneeded in your example query, it uses the wrong argument type and, in case somewhere else a timestamp is really needed, it's better done with a cast.