CREATE TABLE statement
The CREATE TABLE query 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.
To create a table, you should name and define the columns with their data types.
Let's see what the syntax should look like:
From the syntax above, we learned that to create a new table, we should:
- Specify the name of the new table (table_name).
- Specify the column's name (column1, column2, etc..).
- Following the column’s name, specify the column’s data type (integer, string, date, and so on).
The table name of the column name can't be a keyword unless it is quoted. The unquoted name is case insensitive.
Click here to see the list of keywords.
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.
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
Let us take an example where we create an “employees” table with an employee id, name, and address of the employee:
If you have made the table successfully, you will get the following output:
You can verify it by running this command:
The table will be created with each defined column as shown below:
employeeid | lastname | firstname | address |
❌ The table is only created with its headers. It doesn't have any value.
Below is an example of creating a client table with values:
You can run the following command to verify the completed request:
As a result, we have a table as seen below:
Let's try creating a table with the column named "language":
This will fail with an error message:
It happens because "language" is a keyword. To use a keyword as a name one must use quotes:
This will succeed. Querying this table will also require using quotes when selecting this column:
Names within quotes are case-sensitive. That means that this query will fail: