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
