weird problem with PG 8.1

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

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.

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

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