I have been using Aqua Data Studio 7 since its release (and had used a couple versions before that I believe). I really enjoy using ADS, but have been wanting a new version for quite a while. Today while browsing their site I saw that a version 9 beta is available and also that they have a FREE license available for OSS developers. (They also offer student licensing). This is seriously quality software that makes my life so much easier. If you work with a variety of database systems, you can’t do much better than Aqua Data Studio to talk to all of them with one program.
If you want to copy a table, all of its data and all of its constraints, indexes, defaults, etc., you have a few options in postgresql
Create table has a couple forms that are helpful:
This form will create a table like yours with all your data, but without any sequences, indexes, etc on it. As such it is very quick for copying data and nothing else.
CREATE TABLE new_blah AS SELECT * FROM blah;
This form will create a table without any of the data but with all appropriate schema objects. Be careful, this will cause serial columns on the table you are creating to point toward sequence values of the table you are copying.
CREATE TABLE new_blah ( LIKE blah INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
ALTER TABLE new_blah ALTER id DROP DEFAULT;
CREATE sequence new_blah_seq;
-- do what you actually need here, 1 might be a good first id for your situation
SELECT setval('new_blah_seq', (SELECT max(id) FROM blah), true);
ALTER TABLE new_blah ALTER ID SET DEFAULT nextval('new_blah_seq');
Data can then be inserted with the following, but this could take a long time on a large table because all constraints and indexes are checked/built one at a time for each row of data.
INSERT INTO new_blah (SELECT * FROM blah);
The next option is to have all your constraints and indexes be declared externally so that they can be re-added later. This allows the CREATE TABLE AS syntax to do its quick inserts and then you can reinstate all schema objects around this table after the data has been copied. This seems to be the absolutely fastest way to copy a table, but requires you to manage your schema objects outside of postgresql or to pg_dump the schema, do some tricky find and replace on names, and then re-run each schema create/alter command.
The final option is to pg_dump the tables you wish to copy, rename the originals and then restore the copy that you made. This takes longer because it must write the data to disk twice (once into the stream/file you dump to then once again when you run the restore and it gives the results