Postgres Pro
Downloads
Home   >   mailing lists

Thread: weird problem with PG 8.1

weird problem with PG 8.1

From
Marcin Krol
Date:
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

Re: weird problem with PG 8.1

From
Scott Marlowe
Date:
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.

Re: weird problem with PG 8.1

From
Tom Lane
Date:
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

Re: weird problem with PG 8.1

From
Guillaume Lelarge
Date:
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

Re: weird problem with PG 8.1

From
Osvaldo Kussama
Date:
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