Completely copying a postgres table with SQL

user_832218 0
Viewed 100892

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.

data-migration postgresql sql
7 Answers

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;
user_9811830
Related