Hi Everyone,
I would like to ask for your help finding a temporary solution for my problem.
I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows.
The following script is a simplification of my real case:
create table tmp_1 (field1 integer, field2 integer);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 3);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 4);
insert into tmp_1 values (1, 1029);
insert into tmp_1 values (1, 1101);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 3);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 4);
insert into tmp_1 values (13, 1029);
insert into tmp_1 values (13, 1101);
analyze tmp_1;
SELECT distinct field2 FROM tmp_1 WHERE field1 = 13;
The result in postgres 8.3 is as follows:
3
4
1029
1101
And it stays the same no matter what the physical order of the records is in the table. I can do random update and I
getthe same results. It looks like the result is ordered by the distinct fields...
The result in postgres 9.0 is as follows:
3
4
1101
1029
not ordered by the distinct fields nor physical order...
I am wondering if there is a temporary solution (updates, indexes, ...) to order the result by field1 without changing
thestatement...
Thank you in advance,
Ioana Danes