CREATE TABLE Syntax

1. Rules and Data Types

The rules for the names and columns can be a bit different depending on the type of database so it is important to consider looking at the documentation from the database that you intend to use. There are some common rules that are consistent across many different databases. These include:

• The table and column names must start with a letter.
• The table and column names can only contain letters, numbers, and underscores.
• The table and column names should not contain spaces (some databases may allow this).
• The table and column names have a limited length of characters that could be used.

For best practices, spaces should not have spaces in the table or column names. Otherwise, there are some characters depending on the database that would need to be used to enclose the table or column name.

For each column or field in a table, we must identify the type of data that the column can store along with its length in some cases. The most common data types are:

• Boolean – Stores true, false, or null (no value).
• CHAR(n) – This data type is a fixed-length character of length n with space added. If a string is added to the column that is shorter than the string, PostgreSQL pads extra spaces up to the length (n) of the column. If we try to insert a value that is longer than the length of the column, an error will be generated.
• VARCHAR(n) is a variable-length character string that can store up to n characters. Note that with VARCHAR, unlike CHAR, PostgreSQL does not pad the spaces when the stored string is shorter than the length of the column.
• TEXT is a variable-length character string that has unlimited length.
• SMALLINT – A small integer is a 2-byte signed integer that has a range between -32,768 to 32,767.
• INT – An integer is a 4-byte integer that has a range between -2,147,483,648 to 2,147,483,647.
• SERIAL – The serial is the same as an integer but PostgreSQL will automatically generate and populate the values into this column. We’ll cover this in an upcoming tutorial.
• Float(n) – This is a floating-point number that has the precision that can be up to n bytes up to 8 bytes.
• Real – is a 4-byte floating-point number.
• Numeric(p,s) is a real number that has p digits with s number of digits that are displayed after the decimal point.
• Date – Stores the dates on its own.
• Time – Stores the time of day values.
• Timestamp – stores both the date and time values.

---

2. CREATE TABLE command

Now that we have some of the core aspects to get started, we can move to the CREATE TABLE syntax. The SQL CREATE TABLE command makes a new table through the process of defining the layout of the table. Similar to the SELECT statement that you’ve learned about, there is a standard structure for the format of the CREATE TABLE statement. After we have the CREATE TABLE statement, we then have the name of the table, the names, data types, and lengths of the columns.

The basic syntax looks like the following:

CREATE TABLE <tablename> ( <column1> <datatype> [constraint], <column2> <datatype> [constraint], … );

In the sample above, the <tablename> and <column> would need to be replaced with the actual table name and column. The <datatype> would be replaced with the data type and size if necessary. The [constraint] is optional and we will get into the different options in the next tutorial. For now, the only constraint you need to be aware of is the PRIMARY KEY constraint. The PRIMARY KEY constraint uniquely identifies a row in a table. There is an open parenthesis after the <tablename> to signify the starting of the column list and then a close parenthesis after all of the columns and constraints have been created. If the CREATE TABLE statement is missing either one, it will raise an error.

To make the code easier to read, it is good practice to have one line per column or attribute. If we wanted to create a new table called contact that had the contact_id as the primary key and two additional columns with the username and password, the statement could look like the following:

CREATE TABLE contact( contact_id int PRIMARY KEY, username VARCHAR(50), password VARCHAR(50) );

In doing so and running the command, you should see a message similar to the following:

In the Schema Browser, you should see the contact table listed after the album and artist table:

A table could have just one column or many columns, it depends on what you are trying to create. For example, you could have a newsletter table that just consists of emails:

CREATE TABLE newsletter( email VARCHAR(50) );

Although this is the basic syntax, there are other criteria that we’ll explore in upcoming tutorials.

---

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next try your own choices for which columns you want the query to provide.