DISCLAIMER: This question is similar to the stack overflow question here, but none of those answers work for my problem, as I will explain later.
I'm trying to copy a large table (~40M rows, 100+ columns) in postgres where a lot of the columns are indexed. Currently I use this bit of SQL:
CREATE TABLE <tablename>_copy (LIKE <tablename> INCLUDING ALL);
INSERT INTO <tablename>_copy SELECT * FROM <tablename>;
pg_dump -t x2 | sed 's/x2/x3/g' | psql
Well, you're gonna have to do some of this stuff by hand, unfortunately. But it can all be done from something like psql. The first command is simple enough:
select * into newtable from oldtable
This will create newtable with oldtable's data but not indexes. Then you've got to create the indexes and sequences etc on your own. You can get a list of all the indexes on a table with the command:
select indexdef from pg_indexes where tablename='oldtable';
Then run psql -E to access your db and use \d to look at the old table. You can then mangle these two queries to get the info on the sequences:
SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(oldtable)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum;
Replace that 74359 above with the oid you get from the previous query.