Обсуждение: weird problem with PG 8.1
Hello everyone, I'm having this completely weird problem that ORDER BY doesn't seem to work correctly in PG 8.1 as bundled in RedHat 5. When I issue: SELECT * FROM virtualization; I get all the fields: reservations=# SELECT * FROM virtualization; id | Virtualization | color ----+-----------------+--------- 1 | BOX | #FAFAFA 2 | LPAR | #999999 3 | BOX ZONE HOST | #FAFAFA 4 | NPAR | #9966CC 5 | VPAR | #9966CC But when I try to order by column Virtualization: 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. The 'virtualization' table is just a normal table with VARCHAR column of Virtualization: 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) When I try to specify table.column I get this: reservations=# SELECT * FROM virtualization ORDER BY virtualization.Virtualization; ERROR: column virtualization.virtualization does not exist What's going on? Regards, mk
On Tue, Mar 31, 2009 at 9:35 AM, Marcin Krol <mrkafk@gmail.com> wrote: > Hello everyone, > > I'm having this completely weird problem that ORDER BY doesn't seem to work > correctly in PG 8.1 as bundled in RedHat 5. > > When I issue: > > SELECT * FROM virtualization; > > I get all the fields: > > reservations=# SELECT * FROM virtualization; > id | Virtualization | color > ----+-----------------+--------- > 1 | BOX | #FAFAFA > 2 | LPAR | #999999 > 3 | BOX ZONE HOST | #FAFAFA > 4 | NPAR | #9966CC > 5 | VPAR | #9966CC > > But when I try to order by column Virtualization: > > 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. Since you named it "Virtualization" you now get to quote it whenever you reference it. Virtualization, without quotes, case folds to virtualization, which doesn't exist. "Virtualization" will allow you to reference it.
Marcin Krol <mrkafk@gmail.com> writes: > When I issue: > SELECT * FROM virtualization; > I get all the fields: > reservations=# SELECT * FROM virtualization; > id | Virtualization | color > ----+-----------------+--------- > But when I try to order by column Virtualization: > reservations=# SELECT * FROM virtualization ORDER BY Virtualization; You need SELECT * FROM virtualization ORDER BY "Virtualization"; What you typed is a request to sort by the composite row value, which isn't supported in any pre-8.4 release. You might care to go re-read the manual about identifier quoting and case folding. regards, tom lane
Hi, Le mardi 31 mars 2009 à 17:35:58, Marcin Krol a écrit : > [...] > I'm having this completely weird problem that ORDER BY doesn't seem to > work correctly in PG 8.1 as bundled in RedHat 5. > > When I issue: > > SELECT * FROM virtualization; > > I get all the fields: > > reservations=# SELECT * FROM virtualization; > id | Virtualization | color > ----+-----------------+--------- > 1 | BOX | #FAFAFA > 2 | LPAR | #999999 > 3 | BOX ZONE HOST | #FAFAFA > 4 | NPAR | #9966CC > 5 | VPAR | #9966CC > > But when I try to order by column Virtualization: > > 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. > You should put double quotes for the column name because of the uppercase V in its name. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
2009/3/31 Marcin Krol <mrkafk@gmail.com>: > Hello everyone, > > I'm having this completely weird problem that ORDER BY doesn't seem to work > correctly in PG 8.1 as bundled in RedHat 5. > > When I issue: > > SELECT * FROM virtualization; > > I get all the fields: > > reservations=# SELECT * FROM virtualization; > id | Virtualization | color > ----+-----------------+--------- > 1 | BOX | #FAFAFA > 2 | LPAR | #999999 > 3 | BOX ZONE HOST | #FAFAFA > 4 | NPAR | #9966CC > 5 | VPAR | #9966CC > > But when I try to order by column Virtualization: > > 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. > > > The 'virtualization' table is just a normal table with VARCHAR column of > Virtualization: > > 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) > > > When I try to specify table.column I get this: > > reservations=# SELECT * FROM virtualization ORDER BY > virtualization.Virtualization; > ERROR: column virtualization.virtualization does not exist > > > > What's going on? > Try: SELECT * FROM virtualization ORDER BY virtualization."Virtualization"; From the manual: "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 Osvaldo