CREATE TABLE

So far in the series, we have been using a sample database pre-built to run queries. Now we are going to learn some useful ways to create our own table and how to fill them with data. We begin with the CREATE TABLE statement.

The CREATE TABLE statement is used to create a table within a database. We can create a table from scratch or use a query from another table as an input to create a new table.

CREATE TABLE SYNTAX

In order to create a table with the CREATE TABLE statement, we need to define the column/field names and their data types. Below is an example of the CREATE TABLE statement for a table that will house a selection of products.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(8,2) NOT NULL,
    cost DECIMAL(8,2),
    stock_quantity INT DEFAULT 0,
    supplier_id INT,
    launch_date DATE,
    weight_kg DECIMAL(5,2),
    is_discontinued BOOLEAN DEFAULT FALSE,
    rating DECIMAL(2,1)
);

Implementing this query on a Postgres database or even SQLite DB would return a message like below:

CREATE TABLE    






This of course means that your table has been created successfully. There is no data in the table yet. We can
     confirm this by running the select * from products and see the results. It will return the following.



SELECT *
FROM products


 
 
product_id | product_name | category | price | cost | stock_quantity | supplier_id | launch_date | weight_kg | is_discontinued | rating
------------+--------------+----------+-------+------+----------------+-------------+-------------+-----------+-----------------+--------
(0 rows)






    
CREATE TABLE FROM QUERY

    

        Another powerful alternative is to create tables using queries that
        return data from existing tables. This approach is much more
        straightforward as it relieves you from having to manually define the
        correct data types—the new table automatically inherits the data types
        from the source table.
    

    
The example below demonstrates how to create a table from another table use the empty table we just created.
        Notice the slight syntax change
        in using the keyword AS
    


    
CREATE TABLE products_dup AS
SELECT *
FROM products;






    
We can then quickly check if this table has been created by selecting all the information and passing the table
        name

    
SELECT * FROM products_dup;


    
select * from products_dup;
product_id | product_name | category | price | cost | stock_quantity | supplier_id | launch_date | weight_kg | is_discontinued | rating
------------+--------------+----------+-------+------+----------------+-------------+-------------+-----------+-----------------+--------


    
We definitely see that the table has been created and mirrors the table products. There
        are other ways to
        create other types of tables such as views or materialized views
        . These largely
        follow the same syntax, so we may cover them in extra sessions.


    

        This concludes our comprehensive discussion on the
        CREATE TABLE statement. In the next section, we
        will explore IF NOT EXISTS as a best practice
        for creating tables and databases safely without encountering errors.