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

Question

I am trying to set up temporary tables for unit testing purposes. So far, I have managed to create a temporary table that copies the structure of an existing table:

CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING DEFAULTS); 

But there is not enough data from the source table for this. I can copy the data to a temporary table using the CREATE TABLE AS statement instead:

 CREATE TEMP TABLE t_mytable AS SELECT * FROM mytable; 

But then the t_mytable structure t_mytable not be identical, for example, the column sizes and default values ​​will be different. Is there one statement that copies everything?

Another problem with the first query using LIKE is that the key column still refers to the SEQUENCE source table and thus increases it when inserted. Is there an easy way to create a new table with its own sequence, or will I need to set a new sequence manually

Answer 1

Postgres 10 or later

Postgres 10 introduced SQL IDENTITY columns (with small extensions). The ID column of your table will look something like this:

 id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY 

The syntax is in the manual.
Using this instead of the traditional serial column avoids sequence problems. IDENTITY columns use exclusive, highlighted sequences automatically, even when the specification is copied using LIKE . Leadership:

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

And:

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

The solution has become easier:

 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 shown, you can still use setval() to set the current value of the sequence. One SELECT does the trick. Use pg_get_serial_sequence() to get the name of the sequence.

db <> fiddle here

Connected with:

• How to reset postgres primary key sequence if they are not synchronized?
• Is there a shortcut for SELECT * FROM?
• Create a PostgreSQL sequence for a field (which is not a record identifier)

---

Original (old) answer

You can take a create script from a database dump or a graphical user interface such as pgAdmin (which performs reverse engineering of scripts to create database objects), create an identical copy (with a separate sequence for the serial column), and then run:

 INSERT INTO new_tbl SELECT * FROM old_tbl; 

A copy cannot be 100% identical if both tables are in the same schema. Obviously, the table name must be different. Index names will also conflict. Getting serial numbers from the same sequence is probably also not in your interest. So you should (at least) customize the names.

Placing a copy in another schema avoids all of these conflicts. When you create a temporary table from a regular table, as you have shown, this automatically happens because the temporary tables are in their own temporary scheme.

Or look at Francisco's answer to DDL code that needs to be copied directly.

Answer 2

I am using the following code for this:

 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;