PostgreSQL Create Table: Create a duplicate copy of countries table including structure and data by name dup_countries

4. Write a SQL statement to create a duplicate copy of countries table, including structure and data by name dup_countries.

Sample Solution:

Code:

-- This SQL statement creates a new table called 'dup_countries' by copying both the structure and data from the 'countries' table.

CREATE TABLE dup_countries -- Creates a new table named 'dup_countries'.
AS -- Specifies that the following query will be used to generate the table.
SELECT * -- Selects all columns and rows from the 'countries' table.
FROM countries; -- Specifies the source table from which data is being copied.

Explanation:

• The CREATE TABLE ... AS SELECT statement creates a new table based on the result set of a query.
• In this case, the result set is generated by selecting all columns and rows from the existing 'countries' table.
• The new table 'dup_countries' will have the same structure and contain the same data as the 'countries' table.
• This effectively duplicates the 'countries' table into a new table named 'dup_countries'.

Output:

postgres=# CREATE TABLE dup_countries
postgres-# AS SELECT *
postgres-# FROM  countries;
SELECT 0

Here is the command to see the structure of the created table :

postgres=# \d dup_countries
           Table "public.dup_countries"
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 country_id   | character varying(3)  |
 country_name | character varying(45) |
 region_id    | numeric(10,0)         |

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

Previous: Write a sql statement to create the structure of a table dup_countries similar to countries.
Next: Write a SQL statement to create a table countries, set a constraint NULL.