This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script for creating a copy of table including all data, constraints, indexes of a PostgreSQL source table.
I have seen that people are using simple CREATE TABLE AS SELECT… for creating a duplicate table.
But it will create a table with data and column structure only.If we want a duplicate table with all possible structures like column, constraint, index, we should add INCLUDING ALL.
Check the below demonstration:
Create sample table with default, constraint, index:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tbl_A ( ID INT ,Name CHARACTER VARYING DEFAULT 'dbrnd' ,Subject CHARACTER VARYING CONSTRAINT chk_tbl_A_Subject CHECK (Subject IS NOT NULL) ,CONSTRAINT pk_tbl_A_ID PRIMARY KEY(ID) ); CREATE INDEX idx_tbl_A_Subject ON tbl_A USING BTREE (Subject); |
Create another simple copy table:
|
1 |
CREATE TABLE tbl_B AS SELECT * FROM tbl_A; |
Check the table definition of tbl_b:
You cannot find any default, primary key, check or index. It just copied the plain columns.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE public.tbl_b ( id integer, name character varying COLLATE pg_catalog."default", subject character varying COLLATE pg_catalog."default" ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; |
Create another copy table using INCLUDING ALL:
|
1 |
CREATE TABLE tbl_C (LIKE tbl_A INCLUDING ALL); |
Check the table definition of tbl_c:
You can find the definition of all constraints and index.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE public.tbl_c ( id integer NOT NULL, name character varying COLLATE pg_catalog."default" DEFAULT 'dbrnd'::character varying, subject character varying COLLATE pg_catalog."default", CONSTRAINT tbl_c_pkey PRIMARY KEY (id), CONSTRAINT chk_tbl_a_subject CHECK (subject IS NOT NULL) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; |
