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

sql postgresql temp-tables create-table
Stefan Majewsky picture Stefan Majewsky · Sep 4, 2012 · Viewed 13.7k times · Source

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

Francisco Puga picture Francisco Puga · Sep 4, 2013

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;

Related questions

Creating temporary tables in SQL

I am trying to create a temporary table that selects only the data for a certain register_type. I wrote this query but it does not work: $ CREATE TABLE temp1 (Select egauge.dataid, egauge.register_type, egauge.timestamp_localtime, egauge.…

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More