From: | "Andrus" <eetasoft(at)online(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Clustered table order is not preserved on insert |
Date: | 2006-04-26 19:45:49 |
Message-ID: | e2ojqe$17e4$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2006-04-26 20:05:09 | Re: Clustered table order is not preserved on insert |
Previous Message | krzysieq | 2006-04-26 19:34:59 | PostgreSQL 8.1 + PHP5.1.x/4.4.2 + Apache 2.0.55/1.3.34 PROBLEM!! PLEASE HELP |