Clustered table order is not preserved on insert

Поиск
Список
Период
Сортировка
От Andrus
Тема Clustered table order is not preserved on insert
Дата
Msg-id e2ofnp$m5b$1@news.hub.org
обсуждение исходный текст
Ответы Re: Clustered table order is not preserved on insert  (Douglas McNaught <doug@mcnaught.org>)
Re: Clustered table order is not preserved on insert  (Richard Huxton <dev@archonet.com>)
Список pgsql-general
I have table of reports

CREATE TABLE report (
ReportName CHAR(5) not null check (reportname<>''),
< a lot of other fields >,
id serial primary key
)

I want to duplicate report so that id order is preserved.

BEGIN;
CREATE temp TABLE tempreport AS
      SELECT * FROM report
      WHERE reportname='oldr'
      ORDER BY id;

ALTER TABLE tempreport DROP COLUMN id;
update tempreport set  reportname='newr';
insert into report SELECT * FROM tempreport;
DROP TABLE tempreport;
COMMIT;

SELECT *
FROM  report
WHERE reportname='newr'
ORDER BY id;

Observed:

order of some rows in newr is different than in oldr

Expected:

newr must have exactly the same order since
CREATE temp TABLE tempreport AS  .... ORDER BY id
creates clustered table.

Is this best method to preform this?
Why postgres 8.1.3 changes order ?
How to preserve order in newr without adding extra field to report table ?

Andrus.




В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: pg_dump -t <> pg_restore -t
Следующее
От: Douglas McNaught
Дата:
Сообщение: Re: Clustered table order is not preserved on insert