Обсуждение: 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