PostgreSQL Insert Record: Create a duplicate of one table

3. Write a SQL statement to create duplicates of countries table named country_new with all structure and data.

Here in the following is the structure of the table countries.

    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 country_id   | character varying(2)  |
 country_name | character varying(40) |
 region_id    | numeric(10,0)         |

Sample Solution:

Code:

-- This SQL statement creates a new table 'country_new' based on the result of a SELECT query from an existing table 'countries'.

CREATE TABLE country_new
AS SELECT * FROM countries;

Explanation:

• The CREATE TABLE ... AS SELECT statement allows creating a new table based on the result set of a SELECT query.
• country_new is the name of the new table being created.
• SELECT * FROM countries retrieves all columns and rows from the existing table 'countries'.
• The new table 'country_new' will have the same structure (columns and data types) as the 'countries' table, and its contents will be identical to the 'countries' table at the time of execution.

Here is the command to see the structure and list of the inserting rows :

postgres=# \d country_new;
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 country_id   | character varying(2)  |
 country_name | character varying(40) |
 region_id    | numeric(10,0)         |
 

postgres=# SELECT * FROM country_new;
 country_id | country_name | region_id
------------+--------------+-----------
 C1         | India        |      1002
 C2         | USA          |
(2 rows)

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to insert one row into the table countries against the column country_id and country_name.
Next: Write a SQL statement to insert NULL values into region_id column for a row of countries table.