Can't adapt type 'set'

[Naddiseo]

Psycopg2 should be able to adapt sets, and treat them as an iterable, like a list.

Python 3.4.3 (default, Mar 26 2015, 22:03:40) 
[GCC 4.9.2] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> psycopg2.__version__
'2.6.1 (dt dec pq3 ext lo64)'
>>> psycopg2.extensions.adapt(set())
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: can't adapt type 'set'

Background: I'm trying to convert a django app, which currently uses mysql, to use postgres. In places we pass in a set. Mysql treats the set as a plain iterable, and is equivilent to using list(set()).

[dvarrazzo]

Youcan adapt sets no problem: follow the instruction about registering a new adapter at http://initd.org/psycopg/docs/advanced.html#adapting-new-python-types-to-sql-syntax and make adapt(set) to adapt as adapt(list(set)) if that's what you want.

I don't have a computer handy to write an example now but it should be fairly easy. On 10 Aug 2015 9:21 pm, "Richard Eames" notifications@github.com wrote:

Psycopg2 should be able to adapt sets, and treat them as an iterable, like a list.

Python 3.4.3 (default, Mar 26 2015, 22:03:40) [GCC 4.9.2] on linux Type "help", "copyright", "credits" or "license" for more information.

import psycopg2 psycopg2.version '2.6.1 (dt dec pq3 ext lo64)' psycopg2.extensions.adapt(set()) Traceback (most recent call last): File "", line 1, in psycopg2.ProgrammingError: can't adapt type 'set'

Background: I'm trying to convert a django app, which currently uses mysql, to use postgres. In places we pass in a set. Mysql treats the set as a plain iterable, and is equivilent to using list(set()).

— Reply to this email directly or view it on GitHub https://github.com/psycopg/psycopg2/issues/340.

[Naddiseo]

That's what I'm doing as a work around, but I think it should be built in.

[dvarrazzo]

On 11 Aug 2015 3:11 pm, "Richard Eames" notifications@github.com wrote:

That's what I'm doing as a work around, but I think it should be built in.

No: it shouldn't and it won't. Postgres type is an array, with a well defined order and repeated elements. Python type is a set, with unordered distinct elements. Psycopg gives you flexibility to adapt one into the other but they are hardly the same thing, or a natural choice for adaptation.

A better choice for sets representation in Postgres could be an hstore with nulls as values for instance. But again, the application of it is hardly a generic one: psycopg makes it possible but doesn't suggest or mandate it.

[Naddiseo]

You make a good point, except in contexts where ordering doesn't matter: WHERE x in set([1, 2, 3]). Now I'm thinking about it, I'm wondering if that particular context should be handled in the django layer. What do you think, django bug?

[matthewapeters]

Python sets are always ordered.

[dvarrazzo]

On 11 Aug 2015 4:30 pm, "Richard Eames" notifications@github.com wrote:

You make a good point, except in contexts where ordering doesn't matter: WHERE x in set([1, 2, 3]).

This is exactly where an hstore would be a better choice: "where x ? hstore" is an operation that can be indexed.

Now I'm thinking about it, I'm wondering if that particular context should be handled in the django layer. What do you think, django bug?

I don't think is anyone's bug: it's a fairly custom requirement and psycopg, and probably django too, allow you to customize their behaviour to implement it. I don't think you can ask for it out-of-the-box.

[dvarrazzo]

On 11 Aug 2015 4:47 pm, "Matt" notifications@github.com wrote:

Python sets are always ordered.

No, they are not. They are hashes, not different from the keys of a dict.

[Naddiseo]

WHERE x in set([1, 2, 3]).

This is exactly where an hstore would be a better choice: "where x ? hstore" is an operation that can be indexed.

Unless I'm misunderstanding something about database design, no, it wouldn't. If the field is already an index, using a hstore doesn't make sense, consider the following:

# Django ORM:
>>> model_ids = get_a_list_of_models_ids() # [1, 1, 2, 3, 3, 1]
>>> Model.objects.filter(pk__in = set(model_ids)) # Should return 3 objects 
>>> # (Expected) Generated SQL
>>> # SELECT * FROM model_table WHERE id IN (1, 2, 3)

[dvarrazzo]

On 11 Aug 2015 5:15 pm, "Richard Eames" notifications@github.com wrote:

WHERE x in set([1, 2, 3]).

This is exactly where an hstore would be a better choice: "where x ? hstore" is an operation that can be indexed.

Unless I'm misunderstanding something about database design, no it wouldn't. If the field is already an index, using a hstore doesn't make sense, consider the following: [...]

No, my example was about a field with stored hstore sets and a query looking for the records containing a specific item: it is sort of the opposite of your example, where no, hstore doesn't make sense.

However we are going off-topic here: in your case it is enough to convert the set into a list (to use the '= any' operator) or tuple (to use the IN operator). Other people may want to do something different with their sets.

[matthewapeters]

My bad. I was under the false impression that sets ordered their values to reinforce uniqueness more efficiently - a quick test proves me wrong.

Does PostgreSQL even support an analogy to a "set" data-type? I think this is the result of porting a MySQL data type (https://dev.mysql.com/doc/refman/5.0/en/set.html) to a non-existent structure. The Hstore documentation looks like converting a set to the keys of an hstore makes pretty good sense (http://www.postgresql.org/docs/9.0/static/hstore.html). In the end - the "set" could be "pickled" as a text field, and converted back to a set on read.

[boxed]

I ended up here after a little googling on the error message mentioned here. Might I suggest changing the error message to refer to the manual or mentioning that the fix is to "register a new adapter"?

[dvarrazzo]

@boxed weird you didn't end up on the FAQ. Feel free to propose better wording.

[OrangeDog]

I can confirm that this issue is the only result Google gives me for "psycopg2.ProgrammingError: can't adapt type 'set'"