w3resource

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:

CREATE TABLE country_new
AS SELECT * FROM countries;

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.

What is the difficulty level of this exercise?