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>;
This method has two issues:
The table size makes indexing a real time issue. It also makes it infeasible to dump to a file to then re-ingest. I also don't have the advantage of a command line. I need to do this in SQL.
What I'd like to do is either straight make an exact copy with some miracle command, or if that's not possible, to copy the table with all contraints but without indices, and make sure they're the constraints 'in spirit' (aka a new counter for a SERIAL column). Then copy all of the data with a SELECT *
and then copy over all of the indices.
Sources
Stack Overflow question about database copying: This isn't what I'm asking for for three reasons
pg_dump -t x2 | sed 's/x2/x3/g' | psql
and in this setting I don't have access to the command linedefault nextval('x1_id_seq'::regclass)
Method to reset the sequence value for a postgres table: This is great, but unfortunately it is very manual.
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.