Re: Clustered table order is not preserved on insert

Andrus wrote:
> 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.

Tables aren't ordered by definition. If you want to get results back in
a particular order use ORDER BY, possibly wrapped in a view.

> 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

Yes

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

And the INSERT INTO ... SELECT didn't ask for any order.

If you really want to do this, then try something like

-- Don't drop the id column
UPDATE tempreport SET ...;
INSERT INTO report SELECT reportname,... FROM tempreport ORDER BY id;
DROP TABLE tempreport;

> Is this best method to preform this?
> Why postgres 8.1.3 changes order ?

There is no order inside a table - you must supply your own.

Although the solution I describe should work it's still not a good idea.
The reason you are having this problem is that you are trying to do two
things with one column. You are using "id" as a unique ID number and
also as a sort order. If you have a separate sort_order this will let
you duplicate reports as you desire and also allow you to re-arrange
reports without changing their IDs.

Can I recommend getting a book or two on relational theory - "An
Introduction to Database Systems" by Date is widely available.
--
Richard Huxton
Archonet Ltd