Re: Clustered table order is not preserved on insert

why don't you just (not tested)

insert into report (col1,col2,col3) SELECT col1,col2,col3 FROM t2 order by id

This should get the row into report in id order, you need to put in the correct column names

---------- Original Message -----------
From: "Andrus" <eetasoft(at)online(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Sent: Wed, 26 Apr 2006 22:45:49 +0300
Subject: Re: [GENERAL] 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
------- End of Original Message -------