PostgreSQL CREATE TABLE: Creating a new Table

Summary: in this tutorial, you will learn how to design database tables and use the PostgreSQL CREATE TABLE statement to create a new table.

Designing tables #

A PostgreSQL database consists of a collection of related tables. Before creating a table in PostgreSQL, you need to answer three most important questions:

• What kind of information are you going to store in the table?
• What properties does this information have?
• What type of data does each of those properties contain?

Suppose you want to manage product inventory:

To do that you can answer these three questions:

1) What kind of information are you going to store?

We are going to store a list of product inventory. To do that we’ll create a table called inventories.

2) What properties does this information have?

Each inventory record has the following information:

• product name
• brand
• quantity
• price

So the inventories table will have four columns:

• name
• brand
• quantity
• price

3) What type of data does each of those properties contain?

Each table column stores a specific type of data:

• The name column stores product names like iPhone, Galaxy, etc. They are strings.
• The brand column stores brands like Apple amd Samsung, etc. These brands are also strings.
• The quantity column store product quantity such as 10, 21, 30. These quantities are integers.
• The price column stores product prices like 899.99 and 299.99. The product prices are decimal numbers.

Here’s the summary of the inventories table:

Create table statement #

In PostgreSQL, the CREATE TABLE statement allows you to create a new table in a database.

Here’s the basic syntax of the CREATE TABLE statement:

CREATE TABLE table_name (
    column_name1 data_type,
    column_name2 data_type
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• CREATE TABLE are keywords that instructs PostgreSQL to create a new table in the database. Keywords can be lower or uppercase. By convention, keywords are uppercase to make them stand out of the statement.
• table_name is an identifier that specifies the name of the table.

By convention, in PostgreSQL, table names use snake case and plural nouns:

• Snake case is a naming convention you write all letters in lowercase and replace each letter with an underscore. For example, products, brands, and product_categories.
• Plural form: table names are plural nouns like products and brands.

After the CREATE TABLE table_names clause, you specify a list of columns within the parentheses (), each separated by a comma.

Here’s basic syntax for defining a column:

column_name data_type

In this syntax, you specify the column name and and its data type. By convention, column names also use snake case.

For example, the following CREATE TABLE statement creates a new table in the PostgreSQL database:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this inventories table:

• The name and brand columns have the data type of VARCHAR, which is variable-length character. The length specifiers 255 and 50 specifies the maximum length the columns can store. For example, if you attempt to insert a name that have more than 255 characters, you’ll encounter an error.
• The quantity column has the type of INT or integer. Its range is from -2,147,483,648 to +2,147,483,647. If you put a value out of this range, you’ll get an error.
• The price column has the DEC(19,2) type. It can store up to a decimal with 19 digits, up to 17 digits before decimal point and 2 digits after the decimal point.

The following table shows the mapping of the column data type in the table design with PostgreSQL’s data types:

Summary #

• A database consists of a collection of tables.
• A table stores a list of records.
• Table names are plural nouns and use snake case.
• Use the PostgreSQL CREATE TABLE statement to create a new table in the database.

Quiz #