Website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Run Oxla in 2 minutes
🏃‍♂️Run Oxla on S3
🛫Multi-node Deployment
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
⛓️SQL Mutations
DELETE
UPDATE
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
🔄Understanding Transactions
📈Public Metrics
⚙️Oxla Configuration File
✨Changelog
Docs powered by Archbee
SQL Reference
SQL Queries

CREATE TABLE statement

15min

Overview

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.

Syntax

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

Let's see what the syntax should look like:

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


From the syntax above, we learned that to create a new table, we should:

  1. Specify the name of the new table (table_name).
  2. Specify the column's name (column1, column2, etc..).
  3. 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.

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.

Syntax
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
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:

Success Output
CREATE TABLE

Query returned successfully in 263 msec.


You can verify it by running this command:

Retrieve all data
SELECT * FROM employees;


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.

#Case 2: Creating a new table with values

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

Create table
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:

Retrieve all data
SELECT * FROM client;


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

Final output
+------------+--------------+------------------+
| 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
CREATE TABLE preferences (language TEXT);


This will fail with an error message:

Error output
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
CREATE TABLE preferences ("language" TEXT);


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

Correct Query
SELECT "language" FROM preferences;


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

Fail query
SELECT "Language" FROM preferences;




Updated 09 Oct 2023
Did this page help you?
PREVIOUS
SQL TABLE
NEXT
Keywords
Docs powered by Archbee
TABLE OF CONTENTS
Overview
Syntax
Constraints
Table index
Examples
#Case 1: Creating a new table
#Case 2: Creating a new table with values
#Case 3: Quoted names
Docs powered by Archbee

©2023 Oxla