Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Arrays of ranges don't adapt well #313

Closed
mjtamlyn opened this issue Apr 30, 2015 · 2 comments

Comments

Projects
None yet
2 participants
@mjtamlyn
Copy link

commented Apr 30, 2015

Django 1.8 has introduced new fields for both ranges and arrays. They both work really well in isolation, but the combination (e.g. int4range[]) does not.

The basic reason for this is that while the following statement works:

insert into r values(ARRAY['[0, 10)']);

This one does not:

insert into rangearray values(ARRAY['[0, 10)']);

It must become

insert into rangearray values(ARRAY['[0, 10)']::int4range[]);

I believe we should be able to fix this at the Django end by overriding the registered adapter for arrays of range types, however if the psycopg2 auto registered adapter doesn't work, this seemed like a bug here.

What are your thoughts? Is this something we should fix, you should fix or both?

More details can be found at https://code.djangoproject.com/ticket/24726, including some pscopg2 example code which generates the error.

@dvarrazzo

This comment has been minimized.

Copy link
Member

commented Jun 3, 2015

What you may do, because you know more precisely the type of range you are adapting, could be to subclass NumericRange:

In [2]: class TypedNumericRange(psycopg2.extras.NumericRange):
   ...:     pg_type = None
   ...:     

In [3]: class Int4NumericRange(TypedNumericRange):
   ...:     pg_type = b'int4range'
   ...:     

and its adapter:

In [4]: class TypedNumericRangeAdapter(psycopg2._range.NumberRangeAdapter):
   ...:     def getquoted(self):
   ...:         return super(TypedNumericRangeAdapter, self).getquoted() + b'::' + self.adapted.pg_type

In [6]: psycopg2.extensions.register_adapter(Int4NumericRange, TypedNumericRangeAdapter)

these should adapt correctly:

In [11]: cur.mogrify("select %s", [[Int4NumericRange(10,20)]])
Out[11]: "select ARRAY['[10,20)'::int4range]"

We didn't use this method because generally a Python user will have numbers in python without a stricter specification of their type. I'll think about introducing something similar for psycopg if it's practical enough. However, because Django does have type specification, it should be possible for it to use this system.

@dvarrazzo

This comment has been minimized.

Copy link
Member

commented Feb 6, 2017

Further discussion is happening in the django bug. The shortcoming reported is common to many uses of arrays of special types; psycopg is not in a good position to solve that, because adaptation is based on types; for python a list is a list is a list, whatever it contains, whereas for postgres lists are of a specific type.

Because the client usually knows what type the list contains, the workaround suggested is to add a cast to the placeholder, e.g. %s::int4range[]. See also #231.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.