Thread: Bug #954: select distinct array

A.Bhuvaneswaran (bhuvansql@myrealbox.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
select distinct array

Long Description
Hi,

Are we unable to select distinct values from an array? If so, how can we select distinct values from an array without
usingsubscripts? Googling didn't help. 

regards,
bhuvaneswaran

Sample Code
test_pg=# SELECT version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

test_pg=# CREATE TABLE array_test(id int[]);
CREATE TABLE
test_pg=# INSERT INTO array_test VALUES ('{1,3}');
INSERT 4338949 1
test_pg=# INSERT INTO array_test VALUES ('{1,4}');
INSERT 4338950 1
test_pg=# INSERT INTO array_test VALUES ('{1,4}');
INSERT 4338951 1
test_pg=# SELECT distinct id from array_test ;
ERROR:  Unable to identify an ordering operator '<' for type 'integer[]'
        Use an explicit ordering operator or modify the query
test_pg=#


No file was uploaded with this report

pgsql-bugs@postgresql.org wrote:
> test_pg=# SELECT distinct id from array_test ;
> ERROR:  Unable to identify an ordering operator '<' for type 'integer[]'
>         Use an explicit ordering operator or modify the query
> test_pg=#

There is no bug here. As the error says, there is currently no ordering
operator for arrays. There is a *possibility* that it will change in
7.4. But it hasn't happened yet, and time is running short, so don't
count on it.

Joe