Michael Bayer wrote: > my guess is that its confusing the names "virtualization" between the > table and column name. im guessing the "field" you send to > order_by() is a string
Correct. I really have not much choice in the matter without somewhat tedious programming, because column for "order by" comes from web app input, and so it's obviously most straightforward to use a string... > otherwise it would render it as > tablename.columnname and would be quoted for the upper cased > Virtualization. The thing is, for PG it would be useful to double-quote strings as well: It turns out to be Postgres-specific problem, I asked on PG-general users group and the reply is that it is standard quirk of Postgres: "Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case" http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS This is works-as-designed problem: reservations=# SELECT * FROM virtualization ORDER BY Virtualization; ERROR: could not identify an ordering operator for type virtualization HINT: Use an explicit ordering operator or modify the query. reservations=# SELECT * FROM virtualization ORDER BY virtualization.Virtualization; ERROR: column virtualization.virtualization does not exist But: reservations=# SELECT * FROM virtualization ORDER BY "Virtualization"; id | Virtualization | color ----+-----------------+--------- 1 | BOX | #FAFAFA 17 | BOX | #FAFAFA 9 | BOX ESX HOST | #FAFAFA 7 | BOX MSVS HOST | #FAFAFA 18 | BOX MSVS HOST | #FAFAFA So there's a humble request/proposal from me for you: would it be possible for SQLA to always quote column names in ORDER BY clauses for PG backend, even when it's a string? In the meantime I will probably just rename the columns to lowercase... Regards, mk > > > > On Mar 31, 2009, at 5:44 AM, Marcin Krol wrote: > >> 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 -~----------~----~----~----~------~----~------~--~---
