Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?