Thread: table restruction
Hello!
I want to restructure a table called study.
this table has the following structure:
Table "study"
Attribute | Type | Modifier
------------------------+------------------------+----------
chilioid | character varying(80) |
instanceuid | character varying(64) |
id | character varying(64) |
studydate | date |
studytime | time |
modality | character varying(2) |
manufacturer | character varying(128) |
referingphysician | character varying(128) |
description | character varying(128) |
manufacturersmodelname | character varying(128) |
importtime | double precision |
chilisenderid | character varying(80) |
accessionnumber | character varying(64) |
institutionname | character varying(128) |
workflowstate | character varying(8) |
flags | character varying(8) |
performingphysician | character varying(128) |
reportingphysician | character varying(128) |
parentoid | character varying(80) |
So , what I have to do is to move the column parentoid between chilioid
and instanceoid. After that operation parentoid is the second column in
the table study.
Can I do any restructuring in the running database or should I dump the
database and create a new changed schema and then put the data from the
dump back?
So this would spent a lot of time, otherwise it works sure.
Is there any oppertunity to restructure the existing database without
using a dump?
Thanks in advance
David
Attachment
Hi! Do you REALLY need to restructure your data for changing columns? Probably you could use views instead. Üdv, Baldvin
Hi! Thanks, to You! Yes I have to do . Now I solved that problem with rename the original table study to _study then create the new right structured table study , Insert into study (chilioid,...,...) SELECT * FROM _study; Ok not elegant but it works. Another questions: Can I change the physical order of the rows in a database? Is the order of a database under all circumstances the same? (in pg) That is essential for my further restruction of the database... Thanks a lot David
Attachment
On Thu, 20 Sep 2001, David M. Richter wrote: > Hi! > > Thanks, to You! > > Yes I have to do . Now I solved that problem with rename the original > table study to _study > then create the new right structured table study , Insert into study > (chilioid,...,...) SELECT * FROM _study; > Ok not elegant but it works. > > Another questions: > Can I change the physical order of the rows in a database? You mean the order the rows are stored on disk? AFAIK, only cluster, and that has a lot of caveats, and doesn't keep the rows in any particular order after that. > Is the order of a database under all circumstances the same? (in pg) Umm, not really. In general under 7.1 and earlier, I think the heap rows are in order of insertion (at least before a vacuum, but I'm not sure about after). > That is essential for my further restruction of the database... Given that sql guarantees nothing about row ordering, I'm not sure what kind of restructuring you're trying to do.
"David M. Richter" wrote: > snip... > Yes I have to do . Now I solved that problem with rename the original > table study to _study > then create the new right structured table study , Insert into study > (chilioid,...,...) SELECT * FROM _study; > Ok not elegant but it works. > > Another questions: > Can I change the physical order of the rows in a database? Look at the entry for the CLUSTER command at http://www.postgresql.org/idocs/index.php?sql-cluster.html > Is the order of a database under all circumstances the same? (in pg) Normally the primary key or an ORDER BY on the SELECT force this according to the use the specific search is for. > > That is essential for my further restruction of the database... Changing columns is no unheard of, but changing row order is only normally done for performance reasons... Could you say why you need this, we suspect you do not in fact NEED it! > > Thanks a lot > > David -- This is the identity that I use for NewsGroups. Email to this will just sit there. If you wish to email me replace the domain with knightpiesold . co . uk (no spaces).