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
Post a Comment