PostgreSQL – Create table from existing table

By mkyong | Updated: May 31, 2010

Viewed: 2,236 (+13 pv/w)

Tags: postgresql

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;