PostgreSQL CTAS

• CREATE TABLE AS -- define a new table from the results of a query
• The PostgreSQL CTAS(CREATE TABLE AS SELECT) statement is used to create a table from an existing table by copying the existing table's columns

Syntax:

The syntax for the CREATE TABLE AS statement in PostgreSQL is:

CREATE TABLE new_table AS 
  SELECT expressions
  FROM existing_tables
  [WHERE conditions];

• CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types associated with the output columns of the SELECT (except that you can override the column names by giving an explicit list of new column names).
• CREATE TABLE AS bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the query just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the query. In contrast, a view re-evaluates its defining SELECT statement whenever it is queried.

PRACTIACAL FOR CREATE TABLE AS 1:

Here we copied school table using two method they are

I.CTAS Method.

II.InsertInto Method.

I.CTAS Method:

Step1.Create a school for practicing

postgres=# CREATE TABLE school(
     SID bigserial NOT NULL,
     NAME character varying(50) NOT NULL
);
CREATE TABLE

Step 2.Insert some  records for practising perpose

INSERT INTO school VALUES(1,'king');
INSERT INTO school VALUES(2,'matric');
INSERT INTO school VALUES(3,'government');
INSERT INTO school VALUES(4,'private');
INSERT INTO school VALUES(5,'velammal');

Step 3.Just view the table

postgres=# SELECT * FROM school;
 sid |    name    
-----+------------
   1 | king
   2 | matric
   3 | government
   4 | private
   5 | velammal
(5 rows)

Step 4.(create a backup) copy table School_bk from school table

postgres=# CREATE TABLE School_bk AS SELECT * FROM School;
SELECT 5

Step 5.check "School_bk" table with "school" table

postgres=# SELECT * FROM School_bk;
 sid |    name    
-----+------------
   1 | king
   2 | matric
   3 | government
   4 | private
   5 | velammal
(5 rows)

yes! we got copied successfully from "school" table using CTAS method

II.Insert into method:

Step 1.(create a backup)copy table (School_bk2) from student table its from another method

SELECT * INTO School_bk2 FROM School;postgres=# SELECT * INTO School_bk2 FROM School;
SELECT 5

Step 2.Check the (school_bk2 table with school table) data is copied whether correct or not from source table(school) with copied table(school_bk2) 

postgres=# select * from school_bk2;
 sid |    name    
-----+------------
   1 | king
   2 | matric
   3 | government
   4 | private
   5 | velammal
(5 rows)

yes! we got succesfully inserted from "school" to "school_bk2" using insert into method