Hello everyone, Now that I have my PG db filled, I'm encountering this exception while trying to use it:
ProgrammingError: (ProgrammingError) could not identify an ordering operator for type virtualization HINT: Use an explicit ordering operator or modify the query. 'SELECT hosts.id AS hosts_id, hosts."IP" AS "hosts_IP", hosts."HostName" AS "hosts_HostName", hosts."Location" AS "hosts_Location", hosts."Architecture_id" AS "hosts_Architecture_id", hosts."OS_Kind_id" AS "hosts_OS_Kind_id", hosts."OS_version_id" AS "hosts_OS_version_id", hosts."Additional_info" AS "hosts_Additional_info", hosts."Column_12" AS "hosts_Column_12", hosts."Column_13" AS "hosts_Column_13", hosts."Email_id" AS "hosts_Email_id", hosts."Username" AS "hosts_Username", hosts."Password" AS "hosts_Password", hosts."Alias" AS "hosts_Alias", hosts."Virtualization_id" AS "hosts_Virtualization_id", hosts."Shareable" AS "hosts_Shareable", hosts."Shareable_between_projects" AS "hosts_Shareable_between_projects", hosts."Notes" AS "hosts_Notes", hosts."CPU" AS "hosts_CPU", hosts."RAM" AS "hosts_RAM", hosts."Column_24" AS "hosts_Column_24", hosts."Batch" AS "hosts_Batch", hosts."ASSET" AS "hosts_ASSET", hosts."Owner" AS "hosts_Owner", hosts."SSH_KEY_PRESENT" AS "hosts_SSH_KEY_PRESENT", hosts."Machine_Type_Model" AS "hosts_Machine_Type_Model", hosts."MAC_ADDRESS_ETH_0" AS "hosts_MAC_ADDRESS_ETH_0", hosts."Physical_Box" AS "hosts_Physical_Box", hosts."Up_n_running" AS "hosts_Up_n_running", hosts."Available" AS "hosts_Available", hosts."Earliest_reservation_id" AS "hosts_Earliest_reservation_id", hosts."Project_id" AS "hosts_Project_id", architecture.id AS architecture_id, architecture."Architecture" AS "architecture_Architecture", os_kind.id AS os_kind_id, os_kind."OS_Kind" AS "os_kind_OS_Kind", os_version.id AS os_version_id, os_version."OS_version" AS "os_version_OS_version", virtualization.id AS virtualization_id, virtualization."Virtualization" AS "virtualization_Virtualization", virtualization.color AS virtualization_color, project.id AS project_id, project."Project" AS "project_Project" \nFROM hosts, architecture, os_kind, os_version, virtualization, project, email \nWHERE hosts."Architecture_id" = architecture.id AND hosts."OS_Kind_id" = os_kind.id AND hosts."OS_version_id" = os_version.id AND hosts."Email_id" = email.id AND hosts."Virtualization_id" = virtualization.id AND hosts."Project_id" = project.id AND hosts."Up_n_running" = %(Up_n_running_1)s AND hosts."Shareable" = %(Shareable_1)s ORDER BY Virtualization DESC, IP ASC' {'Shareable_1': True, 'Up_n_running_1': True} Note the 'ORDER BY Virtualization' clause. The Postgres docs say: 33.13.5. System Dependencies on Operator Classes "PostgreSQL uses operator classes to infer the properties of operators in more ways than just whether they can be used with indexes. Therefore, you might want to create operator classes even if you have no intention of indexing any columns of your data type. In particular, there are SQL features such as ORDER BY and DISTINCT that require comparison and sorting of values. To implement these features on a user-defined data type, PostgreSQL looks for the default B-tree operator class for the data type. The "equals" member of this operator class defines the system's notion of equality of values for GROUP BY and DISTINCT, and the sort ordering imposed by the operator class defines the default ORDER BY ordering. Comparison of arrays of user-defined types also relies on the semantics defined by the default B-tree operator class. If there is no default B-tree operator class for a data type, the system will look for a default hash operator class. But since that kind of operator class only provides equality, in practice it is only enough to support array equality. When there is no default operator class for a data type, you will get errors like "could not identify an ordering operator" if you try to use these SQL features with the data type. " ( http://www.postgresql.org/docs/7.4/static/xindex.html ) But I am not using any user-defined data class. It's just a table with VARCHAR for virtualization.Virtualization column, as created by SQLA: reservations=# \d virtualization Table "public.virtualization" Column | Type | Modifiers ----------------+-------------------+------------------------------------------------------------- id | integer | not null default nextval('virtualization_id_seq'::regclass) Virtualization | character varying | color | character varying | Indexes: "virtualization_pkey" PRIMARY KEY, btree (id) The SQLA query in question: selectexpr = session.query(Host, Architecture, OS_Kind, OS_version, Virtualization, Project) ... if direction == 'descending': selectexpr = selectexpr.filter_by(Up_n_running = True).filter_by(Shareable = True).order_by(desc(field)) else: selectexpr = selectexpr.filter_by(Up_n_running = True).filter_by(Shareable = True).order_by(asc(field)) ..where field happens to be 'Virtualization' by default. The above query works flawlessly with Sqlite, it's just Postgres (8.1) backend that has this problem. Mike! Help! :-) Regards, mk --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---