Thread: Fwd: SELECT MAX with char => BUG?

Hi !

I am quite confused of the results on a SELECT max...

My environment:
Conectiva Linux 10, PostgreSQL 7.4.6 (compiled from the sources)

My problem is the "select max(id) FROM test" the result is 20 but the 
right is 1020. Is this a BUG or I am crazy ??

Cheers,

Rodrigo Carvalhaes

The SQL...

teste=# CREATE TABLE test ( id char(15), name char(80) );
CREATE TABLE
teste=# \d test      Table "public.test"
Column |     Type      | Modifiers
--------+---------------+-----------
id          | character(15) |
name   | character(80) |

teste=# INSERT INTO test VALUES ( '10', 'luidgi');
INSERT 15303727 1
teste=# INSERT INTO test VALUES ( '20', 'luis');
INSERT 15303728 1
teste=# INSERT INTO test VALUES ( '1010', 'ruan');
INSERT 15303729 1
teste=# INSERT INTO test VALUES ( '1020', 'lion');
INSERT 15303730 1
teste=# SELECT * FROM test;     id        |                                       name
-----------------+----------------------------------------------------------------------------------
10              | luidgi
20              | luis
1010            | ruan
1020            | lion
(4 rows)

teste=# SELECT max(id) FROM test;
max
-----
20
(1 row)

teste=# select max(id) FROM test;
max
-----
20
(1 row)

Rodrigo Carvalhaes wrote:
> Hi !
> 
> I am quite confused of the results on a SELECT max...
> 
> My environment: Conectiva Linux 10, PostgreSQL 7.4.6 (compiled from
> the sources)
> 
> My problem is the "select max(id) FROM test" the result is 20 but the
>  right is 1020. Is this a BUG or I am crazy ??

Crazy. And posting to the wrong list - try the general/sql lists for 
this sort of thing.

> teste=# CREATE TABLE test ( id char(15), name char(80) ); CREATE

You've defined "id" as char - so it's sorting alphabetically, not 
numerically, so 1111 < 2

Just defined "id" as a numeric type.

--   Richard Huxton  Archonet Ltd

Rodrigo Carvalhaes <grupos@carvalhaes.net> writes:
> My problem is the "select max(id) FROM test" the result is 20 but the 
> right is 1020. Is this a BUG or I am crazy ??

You seem to be confused about the difference between numbers and
character strings.  max() on a char(n) column returns the
lexicographically largest item.
        regards, tom lane