How to copy structure and contents of a table, but with separate sequence?

Question

I'm trying to setup temporary tables for unit-testing purposes. So far I managed to create a temporary table which copies the structure of an existing table:

CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING DEFAULTS);

But this lacks the data from the original table. I can copy the data into the temporary table by using a CREATE TABLE AS statement instead:

CREATE TEMP TABLE t_mytable AS SELECT * FROM mytable;

But then the structure of t_mytable will not be identical, e.g. column sizes and default values are different. Is there a single statement which copies everything?

Another problem with the first query using LIKE is that the key column still references the SEQUENCE of the original table, and thus increments it on insertion. Is there an easy way to create the new table with its own sequence, or will I have to set up a new sequence by hand?

Answer 1

I'm using the following code to do it:

CREATE TABLE t_mytable (LIKE mytable INCLUDING ALL);
ALTER TABLE t_mytable ALTER id DROP DEFAULT;
CREATE SEQUENCE t_mytable_id_seq;
INSERT INTO t_mytable SELECT * FROM mytable;
SELECT setval('t_mytable_id_seq', (SELECT max(id) FROM t_mytable), true);
ALTER TABLE t_mytable ALTER id SET DEFAULT nextval('t_my_table_id_seq');
ALTER SEQUENCE t_mytable_id_seq OWNED BY t_mytable.id;

Answer 2

Postgres 10 or later

Postgres 10 introduced IDENTITY columns conforming to the SQL standard (with minor extensions). The ID column of your table would look something like:

id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY

Syntax in the manual.
Using this instead of a traditional serial column avoids your problem with sequences. IDENTITY columns use exclusive, dedicated sequences automatically, even when the specification is copied with LIKE. The manual:

Any identity specifications of copied column definitions will only be copied if INCLUDING IDENTITY is specified. A new sequence is created for each identity column of the new table, separate from the sequences associated with the old table.

And:

INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.

The solution is simpler now:

CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING ALL);
INSERT INTO t_mytable TABLE mytable;
SELECT setval(pg_get_serial_sequence('t_mytable', 'id'), max(id)) FROM tbl;

As demonstrated, you can still use setval() to set the sequence's current value. A single SELECT does the trick. pg_get_serial_sequence()]6 gets the name of the sequence.

db<>fiddle here

Related:

• How to reset postgres' primary key sequence when it falls out of sync?
• Is there a shortcut for SELECT * FROM?
• Creating a PostgreSQL sequence to a field (which is not the ID of the record)

---

Original (old) answer

You can take the create script from a database dump or a GUI like pgAdmin (which reverse-engineers database object creation scripts), create an identical copy (with separate sequence for the serial column), and then run:

INSERT INTO new_tbl
SELECT * FROM old_tbl;

The copy cannot be 100% identical if both tables reside in the same schema. Obviously, the table name has to be different. Index names would conflict, too. Retrieving serial numbers from the same sequence would probably not be in your best interest, either. So you have to (at least) adjust the names.

Placing the copy in a different schema avoids all of these conflicts. While you create a temporary table from a regular table like you demonstrated, that's automatically the case since temp tables reside in their own temporary schema.

Or look at Francisco's answer for DDL code to copy directly.