Re: weird problem with PG 8.1

2009/3/31 Marcin Krol <mrkafk(at)gmail(dot)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