Re: How to change the structure of tables with

-----Original Message-----
From: NH-Jason [mailto:jason(at)newhonest(dot)com(dot)hk]
Sent: 31 January 2002 07:48
To: pgadmin-support(at)postgresql(dot)org
Subject: [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,

Yes, if you open an SQL window and enter:

SELECT * FROM table

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.

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:

INSERT INTO newtable (col1, col2) VALUES (SELECT col1, col2 FROM oldtable)

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.

HTH, regards,

Dave.