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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

 
--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;
Oracle Magazine (Free)
Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world\'s largest enterprise software company.
Publisher : Oracle Corporation