Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL REATE TABLE AS Statement(CTAS Method)

  • 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 will 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


Comments

Popular posts from this blog

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

Oracle to Postgresql migration

PostgreSQL Enterprise Manager - PEM Monitoring Tools

7 Steps to configure BDR replication in postgresql