Re: Clustered table order is not preserved on insert - Mailing list pgsql-general
From | Richard Huxton |
---|---|
Subject | Re: Clustered table order is not preserved on insert |
Date | |
Msg-id | 444FDBF6.4010505@archonet.com Whole thread Raw |
In response to | Re: Clustered table order is not preserved on insert ("Andrus" <eetasoft@online.ee>) |
List | pgsql-general |
Andrus wrote: >>> 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. " And what has that got to do with the order of SELECTs or INSERTs? Physical storage is an optimisation issue not a logical/functional one. >> 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: Sorry, you can't at present. You only need to do it the once though. > 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; Well, I wouldn't have a temporary view either - just create a normal view. I must admit I still can't see *why* you are duplicating all your reports and what it means to do that. >> 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. It was working by luck before. No RDBMS guarantees ordering without SELECT. Some might pull data out by ID number by chance, but that's all it is. > Table stucture change requires re-writing parts of code, testing, debugging > and creating conversion routines from previous table version. Wrap the base-table in a view that conceals the id and sortorder. Access reports through that view. Your application doesn't need to know anything has changed. > 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? No. In the UK you can order any in-print book at your local library for £1 or so - not sure about your corner of the world though. > 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. Nice to have natural keys, but not always possible. You also need to be careful not to rely on "promises" from other systems that they are providing you with good, guaranteed unique keys. Sometimes they can get it wrong and then you are stuck. > There was no discussion about this in Joel's book You have two concepts in one column here: 1. Each report has a unique code (a number in this case) 2. Each report has a sort ordering. If you have two reports (1,repA), (2,repB) and want to add a third between them then with your system you need to change the ID of (2,repB). Why? The report is the same, so why has it's ID changed? However, if you have a separate sort_num column then you just need to update that, perhaps not even that if you leave gaps. For example: (1, repA, 10), (2, repB, 20) and we add (3, repC, 15). As I said, rename the base-table handle the sorting with a view and your application doesn't need to know anything has changed. -- Richard Huxton Archonet Ltd
pgsql-general by date: