Re: Clustered table order is not preserved on insert

[Richard Huxton <dev(at)archonet(dot)com>]

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