Re: Clustered table order is not preserved on insert

>> I want to duplicate report so that id order is preserved.
>
> Tables aren't ordered by definition.

From CLUSTER docs:

"When a table is clustered, it is physically reordered based on the index
information. "

> If you want to get results back in a particular order use ORDER BY,
> possibly wrapped in a view.

Using wrapper view would be excellent idea!  Thank you.
However, I have some hundred of columns in report table.
It is very tedious to list all those columns in view definition.

How to create a wrapper view so that it returns all columns except id column
without listing all columns in SELECT clause ? How to implement EXCEPT
COLUMNS clause like:

CREATE TEMP VIEW reportwrapper AS
SELECT  * EXCEPT COLUMNS (id, reportname),
   'newr' as reportname
FROM reports
WHERE reportname='oldr'
ORDER BY id

INSERT INTO reports SELECT * FROM reportwrapper;


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

Since id is primary key field, I got duplicate primary key error when it is
not dropped!

How to preserve order ant let postgres to generate primary keys without
adding extra order field?

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

Thank you. This is good explanation.
However, this ill-designed structure is used in a lot of different sites and
now it suddenly stops working.
Table stucture change requires re-writing parts of code, testing, debugging
and creating conversion routines from previous table version.

So I'll prefer some other solution if possible.

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

Is it possible to read this form internet?
I have read Joel  Celkos book "SQL Programming Style".
Among other things Joel wrote  that every table must have only natural
primary keys. No surrogates, no ids.
There was no discussion about this in Joel's  book

Andrus.