​
Overview

The CREATE TABLE statement is used to create a new table. Each table contains one or more columns, and each has a data type that defines the data that can be stored, e.g., numbers, strings, or date data.

The table stores the data in a database, so ensure that you already have one.

​
Syntax

To create a table, you should name and define the columns with their data types.

CREATE TABLE [ IF NOT EXISTS ] table_name(
  column_1 datatype,
  column_2 datatype,
  column_3 datatype,
  .....
);

From the syntax above:

  • table_name: Specify the table name.
  • column_1, column_2, column_n: Specify the column’s name.
  • datatype: The data type of the column.
  • IF NOT EXISTS (Optional): Use IF NOT EXISTS to conditionally create the new table only if it does not exist. Attempting to create a new table that already exists without using the IF NOT EXISTS option will result in an error.
The table or column name cannot be a keyword unless it is quoted. Unquoted names are case-insensitive. Click here to see the list of keywords.

​
Constraints

When creating a table, we can add the following column constraints: NOT NULL – ensures that values in a column cannot be NULL and will always contain a value. In other words, if you don’t define NOT NULL, the column can be empty/have no value/NULL.

CREATE TABLE table_name(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
column3 datatype NOT NULL,
.....
);

​
Table index

The freshly created table can contain an index. More detail here.

By default, the table will be created in the public schema. Furthermore, you can also create and specify tables to other specific schemas. Click here for more info.

​
Examples

​
Case #1: Creating a new table

Let us take an example where we create an “employees” table with an employee id, name, and address of the employee:

CREATE TABLE employees (
    employeeID int,
    lastName string,
    firstName string NOT NULL,
    address string
);

If you have made the table successfully, you will get the following output: 

CREATE TABLE
Query returned successfully in 263 msec.

You can verify it by running this command:

SELECT * FROM employees;

The table will be created with each defined column as shown below:

employeeidlastnamefirstnameaddress
The table is only created with its headers. It doesn’t have any value.

​
Case #2: Creating a new table with values

Below is an example of creating a client table with values:

CREATE TABLE client (
  client_id int,
  client_name string NOT NULL,
  client_origin string
);
INSERT INTO client 
    (client_id, client_name, client_origin) 
VALUES 
    (181891,'Oxla','Poland'),
    (181892,'Google','USA'),
    (181893,'Samsung','South Korea');

You can run the following command to verify the completed request:

SELECT * FROM client;

As a result, we have a table as seen below:

+------------+--------------+------------------+
| client_id  | client_name  | client_origin    |
+------------+--------------+------------------+
| 181891     | Oxla         | Poland           |
| 181892     | Google       | USA              | 
| 181893     | Samsung      | South Korea      |
+------------+--------------+------------------+

​
Case #3: Quoted names

Let’s try creating a table with the column named “language”:

CREATE TABLE preferences (language TEXT);

This will fail with an error message:

ERROR:  syntax error, unexpected LANGUAGE, expecting IDENTIFIER (LINE:1COLUMN:27)
CREATE TABLE preferences (language TEXT);
                          ^
LINE 1: CREATE TABLE preferences (language TEXT);
                                 ^

It happens because “language” is a keyword. To use a keyword as a name one must use quotes:

CREATE TABLE preferences ("language" TEXT);

This will succeed. Querying this table will also require using quotes when selecting this column:

SELECT "language" FROM preferences;

Names within quotes are case-sensitive. That means that this query will fail:

SELECT "Language" FROM preferences;

​
Case #4: Creating a New Table Using IF NOT EXISTS

To avoid errors when the table already exists, use the IF NOT EXISTS option. Here is how it works:

​
Example without IF NOT EXISTS

  1. First, create the table without using the IF NOT EXISTS option:
CREATE TABLE products (
  productID int,
  productName string,
  category string NOT NULL,
  price real
);

Output:

CREATE
  1. If you attempt to create the table again without using IF NOT EXISTS, it will result in an error:
CREATE TABLE products (
  productID int,
  productName string,
  category string NOT NULL,
  price real
);

Output:

ERROR:  relation "products" already exists

​
Example with IF NOT EXISTS

Now, create the table using the IF NOT EXISTS option to avoid the error:

CREATE TABLE IF NOT EXISTS products (
  productID int,
  productName string,
  category string NOT NULL,
  price real
);

Using IF NOT EXISTS allows the query to succeed even if the table already exists.

CREATE
SQL TABLEKeywords