> 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.
