Duplicate a Table

You can duplicate a table by running the following the commands below. This copies only the schema, and not the data. Also, the schema is not totally complete. No indexes are copied. But you can add those after per below.

CREATE TABLE new_table AS 
TABLE old_table 
WITH NO DATA;

Assuming an id field on the old table, fix the id field to be autogenerated and a primary key (assuming the old_table had a columan named "id"

ALTER TABLE new_table
ALTER id SET NOT NULL,
ALTER id ADD GENERATED BY DEFAULT AS IDENTITY;

ALTER TABLE new_table
ADD PRIMARY KEY (id);

Sometimes, after creating the table, I need to add some sequences, like some incrementing number. You can use the code below. Sequences can be defined with the data types SMALLINT, INT, or BIGINT. The default data type is BIGINT if not specified. After creating a sequence, you can use functions like nextval(), currval(), and setval() to retrieve or set sequence.

CREATE SEQUENCE new_table_id_seq
START 10000
INCREMENT 1
OWNED BY new_table.id;

Then add the default value to the column. This is critical, so it automatically uses the sequence.

ALTER TABLE the_table
ALTER id SET NOT NULL,
ALTER id SET DEFAULT nextval('new_table_id_seq') ;