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
Re: Clustered table order is not preserved on insert |
Список | 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 по дате отправления: