PostgreSQL Create Table: Create the structure of a table dup_countries similar to countries

3. Write a sql statement to create the structure of a table dup_countries similar to countries.

Sample Solution:

Code:

-- This SQL statement creates a new table called 'dup_countries' by copying the structure of the 'countries' table without copying any data.

CREATE TABLE dup_countries AS 
(
    SELECT * -- Selects all columns and rows from the 'countries' table.
    FROM countries
)  
WITH NO DATA; -- Specifies that the newly created 'dup_countries' table should not contain any data.

Explanation:

• The CREATE TABLE ... AS 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 WITH NO DATA clause specifies that the new table 'dup_countries' should have the same structure as the 'countries' table but should not copy any data.
• This effectively creates an empty table with the same structure as the 'countries' table.

Output:

postgres=# CREATE TABLE dup_countries AS (
postgres(# SELECT *
postgres(# FROM countries)
postgres-# WITH NO DATA;
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 a simple table countries, including columns country_id,country_name and region_id which already exist.
Next: Write a SQL statement to create a duplicate copy of countries table, including structure and data by name dup_countries.