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.