I am trying to query a postgresql (v 9.3.6) table with a tstzrange to determine if a given timestamp exists within the table defined as

CREATE TABLE sensor(
  id serial,
  hostname varchar(64) NOT NULL,
  ip varchar(15) NOT NULL,
  period tstzrange NOT NULL,
  PRIMARY KEY(id),
  EXCLUDE USING gist (hostname WITH =, period with &&)
); 

I am using psycopg2 and when I try the query:

sql = "SELECT id FROM sensor WHERE %s <@ period;"
cursor.execute(sql,(isotimestamp,))

I get the error

psycopg2.DataError: malformed range literal: 
...
DETAIL:  Missing left parenthesis or bracket.

I've tried various type castings to no avail.

I've managed a workaround using the following query:

sql = "SELECT * FROM sensor WHERE %s BETWEEN lower(period) AND upper(period);"

but would like to know why I am having problem with the range operators. Is it my code or psycopg2 or what?

Any help is appreciated.

EDIT 1: In response to the comments, I have attempted the same query on a simple 1-row table in postgresql like below

=> select * from sensor;
session_id | hostname |    ip     |                              period                               
------------+----------+-----------+-------------------------------------------------------------------
1 | bob    | 127.0.0.1 | ["2015-02-08 19:26:42.032637+00","2015-02-08 19:27:28.562341+00")

(1 row)

Now by using the "@>" operator I get the following error:

=> select * from sensor where period @> '2015-02-08 19:26:43.04+00';
ERROR:  malformed range literal: "2015-02-08 19:26:43.04+00"
LINE 1: select * from sensor where period @> '2015-02-08 19:26:42.03...

Which appears to be the same as the psycopg2 error, a malformed range literal, so I thought I would try typecasting to timestamp as below

=> select * from sensor where sensor.period @> '2015-02-08  19:26:42.032637+00'::timestamptz;
session_id | hostname |    ip     |                              period                               

------------+----------+-----------+------------------------------------------------------------------- 1 | feral | 127.0.0.1 | ["2015-02-08 19:26:42.032637+00","2015-02-08 19:27:28.562341+00")

So it appears that it is my mistake, the literal has to be typecast or it is assumed to be a range. Using psycopg2, the query can be executed with:

sql="select * from sensor where period @> %s::timestamptz"    
share|improve this question
1  
Could you show us the real query that has been sent to the database? It looks like psycopg2 doesn't work correct with ranges. – Frank Heikens Feb 15 '15 at 17:25
    
What about the trailing comma in cursor.execute(sql,(isotimestamp,))? – Erwin Brandstetter Feb 15 '15 at 18:26
    
Thanks Frank, just trying to answer your comment I was able to get it working – WraithWireless Feb 16 '15 at 0:37
    
Erwin, (1) is an int as in type((1)) = <type 'int'> and (1,) is a tuple. pscyopg2 requires a tuple – WraithWireless Feb 16 '15 at 1:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.