Completely copying a postgres table with SQL

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:

1. It adds the indices before data ingest, so it will take much longer than creating the table without indices and then indexing after copying all of the data.
2. This doesn't copy `SERIAL' style columns properly. Instead of setting up a new 'counter' on the the new table, it sets the default value of the column in the new table to the counter of the past table, meaning it won't increment as rows are added.

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

1. Stack Overflow question about database copying: This isn't what I'm asking for for three reasons

   • It uses the command line option pg_dump -t x2 | sed 's/x2/x3/g' | psql and in this setting I don't have access to the command line
   • It creates the indices pre data ingest, which is slow
   • It doesn't update the serial columns correctly as evidence by default nextval('x1_id_seq'::regclass)

2. Method to reset the sequence value for a postgres table: This is great, but unfortunately it is very manual.

The create table as feature in PostgreSQL may now be the answer the OP was looking for.

https://www.postgresql.org/docs/9.5/static/sql-createtableas.html

create table my_table_copy as
  select * from my_table

This will create an identical table with the data.

Adding with no data will copy the schema without the data.

create table my_table_copy as
  select * from my_table
with no data

This will create the table with all the data, but without indexes and triggers etc.

---

create table my_table_copy (like my_table including all)

The create table like syntax will include all triggers, indexes, constraints, etc. But not include data.

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.

The closest "miracle command" is something like

pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -

In particular, this takes care of creating the indexes after loading the table data.

But that doesn't reset the sequences; you will have to script that yourself.

To copy a table completely, including both table structure and data, you use the following statement:

CREATE TABLE new_table AS 
TABLE existing_table;

To copy a table structure without data, you add the WITH NO DATA clause to the CREATE TABLE statement as follows:

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

To copy a table with partial data from an existing table, you use the following statement:

CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;

WARNING:

All the answers which use pg_dump and any sort of regular expression to replace the name of the source table are really dangerous. What if your data contains the substring that you are trying to replace? You will end up changing your data!

I propose a two-pass solution:

1. eliminate data lines from the dump using some data-specific regexp
2. perform search-and-replace on the remaining lines

Here's an example written in Ruby:

ruby -pe 'gsub(/(members?)/, "\\1_copy_20130320") unless $_ =~ /^\d+\t.*(?:t|f)$/' < members-production-20130320.sql > copy_members_table-20130320.sql

In the above I am trying to copy "members" table into "members_copy_20130320". My data-specific regexp is /^\d+\t.*(?:t|f)$/

The above type of solution works for me. Caveat emptor...

edit:

OK, here's another way in pseudo-shell syntax for the regexp-averse people:

1. pg_dump -s -t mytable mydb > mytable_schema.sql

2. search-and-replace table name in mytable_schema.sql > mytable_copy_schema.sql

3. psql -f mytable_copy_schema.sql mydb

4. pg_dump -a -t mytable mydb > mytable_data.sql

5. replace "mytable" in the few SQL statement preceding the data section

6. psql -f mytable_data.sql mydb

Apparently you want to "rebuild" a table. If you only want to rebuild a table, not copy it, then you should use CLUSTER instead.

SELECT count(*) FROM table; -- make a seq scan to make sure the table is at least
                            -- decently cached
CLUSTER someindex ON table;

You get to choose the index, try to pick one that suits your queries. You can always use the primary key if no other index is suitable.

If your table is too large to be cached, CLUSTER can be slow though.

create table newTableName (like   oldTableName including indexes); 
insert into newTableName  select * from oldTableName

This worked for me 9.3