Re: Clustered table order is not preserved on insert

> You are never guaranteed any order in a result set unless you use
> ORDER BY in the query.

I cannot use order by since postgres must generate new values for id column.
For this case, id column must not exist in insertable table.

> Because PG treats UPDATE as DELETE + INSERT,
> the table ordering changes all the time.

This is excellent explanation! Thank you.

I changed by code so that clustering is performed after UPDATE command:

CREATE temp TABLE tempreport AS
      SELECT * FROM report
      WHERE reportname='oldr';

UPDATE tempreport SET reportname='newr';
CREATE TEMP TABLE t2 AS SELECT * FROM tempreport ORDER BY id;

ALTER TABLE t2 DROP COLUMN id;
insert into report SELECT * FROM t2;



Will DROP COLUMN preserve table clustering ?

Is it reasonable to  expect that clustered table is inserted in pyhical
order ?
Is it OK to use this code ?

Andrus.