Lists: | pgsql-hackers |
---|
From: | Rodrigo Carvalhaes <grupos(at)carvalhaes(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Fwd: SELECT MAX with char => BUG? |
Date: | 2004-11-20 14:04:02 |
Message-ID: | 419F4ED2.4060704@carvalhaes.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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)
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | grupos(at)carvalhaes(dot)net |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Fwd: SELECT MAX with char => BUG? |
Date: | 2004-11-20 16:19:50 |
Message-ID: | 419F6EA6.1010601@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | grupos(at)carvalhaes(dot)net |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Fwd: SELECT MAX with char => BUG? |
Date: | 2004-11-20 16:55:27 |
Message-ID: | 21503.1100969727@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Rodrigo Carvalhaes <grupos(at)carvalhaes(dot)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