Re: How to change the structure of tables with

[Dave Page]

 
<BLOCKQUOTE dir=ltr 
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; 
MARGIN-RIGHT: 0px">
  
  <FONT 
  face=Tahoma size=2>-----Original Message-----From: NH-Jason 
  [mailto:[EMAIL PROTECTED]] Sent: 31 January 2002 
  07:48To: [EMAIL PROTECTED]: 
  [pgadmin-support] How to change the structure of tables with 
  pgadmin2?
  Hi,
      I'm an in-house VB programmer working 
  with VB + Pg. pgAdmin2 is very valuable to me. 
   
      I have to change the tables' structures 
  (add / delete column name, indexes; change data type ...) from time to time. I 
  understand changing some of the schema thing is impossible with pg, so I will 
  copy the SQL defination of the schema offered by pgAdmin2, drop the table, 
  change the SQL defination and rebuild it. Everything's fine except all the 
  data (mostly testing data) gone. Is there existing pgadmin / plug-in feature 
  is able to do the job for me?
   
Hi 
Jason,
<FONT 
size=2> 
Yes, if you open an SQL 
window and enter:
<FONT 
size=2> 
SELECT * FROM 
table
<FONT 
size=2> 
You can then select 
ASCII Text File as the output format to save to a file. Once you've modified the 
table, use the Import Wizard plugin to re-import tthe data. You should probably 
do some trial runs first to make sure the delimiters/quotes you choose work well 
with your data.
<FONT 
size=2> 
Another way you might 
try (and I haven't tried this btw) is to create your new table 
structure with a temporary name and then use SQL like:
<FONT 
size=2> 
INSERT INTO newtable 
(col1, col2) VALUES (SELECT col1, col2 FROM oldtable)
<FONT 
size=2> 
This way leaves all the 
old and new data on the server of course until you are happy at which point you 
can rename/drop the tables as required.
<FONT 
size=2> 
HTH, 
regards,
<FONT 
size=2> 
<FONT 
size=2>Dave.
<FONT 
size=2> 
<FONT 
size=2>