PostgreSQL – Create table from existing table

PostgreSQL support function like create a new table(with data) from an existing table. It can be achieved with following two syntax.


1) CREATE TABLE 'NEW_TABLE_NAME' AS SELECT * FROM 'TABLE_NAME_YOU_WANT_COPY';

2) SELECT * INTO 'NEW_TABLE_NAME' FROM 'TABLE_NAME_YOU_WANT_COPY' ;

Sometime i also use this method to temporary backup table :), according to PostgresSQL ‘CREATE TABLE AS’ is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax. We also can append ‘Where’ clause in above SQL script like


CREATE TABLE 'NEW_TABLE_NAME' AS SELECT * FROM 'TABLE_NAME_YOU_WANT_COPY' WHERE CONDITION'

I write a simple script to demonstrate the usage of above two CREATE TABLE AS and SELECT INTO methods.

 

--Create a student table for demo
CREATE TABLE STUDENT(
     SID bigserial NOT NULL,
     NAME character varying(50) NOT NULL
)

--Insert two records for testing
INSERT INTO STUDENT VALUES(1,'MKYONG');
INSERT INTO STUDENT VALUES(2,'MKYONG2');

--1,mkyong
--2,mkyong2
SELECT * FROM STUDENT;

--create a backup table (student_bk) from student table
CREATE TABLE STUDENT_BK AS SELECT * FROM STUDENT;

--1,mkyong
--2,mkyong2
SELECT * FROM STUDENT_BK;

--create a backup table (student_bk2) from student table
SELECT * INTO STUDENT_BK2 FROM STUDENT;

--1,mkyong
--2,mkyong2
SELECT * FROM STUDENT_BK2;

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
2 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
James BeckerAlessandro Mecca Recent comment authors
newest oldest most voted
Alessandro Mecca
Guest
Alessandro Mecca

The problem I found with “CREATE TABLE ‘…’ AS SELECT …” is that the column constraints (i.e. not null constraint) are not preserved. A better way is “CREATE TABLE new_table ( LIKE existing_table )”

James Becker
Guest
James Becker

This is dangerous, because new tables will lose crucial constraints and foreign key relations. Instead, you should use CREATE TABLE new_products AS TABLE old_products;