How to Create a Table in PostgreSQL, With Example

This tutorial will show you how to create a table in a PostgreSQL database using the CREATE TABLE statement, and provide a code example. In relational databases, tables hold all of the data stored in the database, so creating them is a fundamental task that you will need to perform regularly. If you are using databases already created by other software, understanding how tables are created is still useful for troubleshooting and understanding how your systems behave.

The PostgreSQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a PostgreSQL database. The arguments passed to this statement specify the table structure and column properties, such as its data type, length, constraints, and default value. The syntax of the CREATE TABLE statement is as follows:

CREATE TABLE table_name (
    column_name data_type [constraint],
    column_name data_type [constraint],
    ...
);

Note that:

• table_name is the name of the new table that we want to create
• column_name is the name for each column to be created in the new table
• After column_name, the properties of the column are specified
  • A columns data type must be specified
  • Other constraints are optional

Example: Creating a Table in a PostgreSQl Database

Below is an example in which a table is created that contains columns to store data about the staff at a business:

CREATE TABLE staff (
    id serial PRIMARY KEY,
    full_name varchar(50) NOT NULL,
    email_address varchar(50) UNIQUE,
    hourly_wage decimal(10,2) CHECK (hourly_wage > 0)
);

Note the details of the columns that will be created in the new table named staff:

• id: A column of the serial data type. Serial columns will be automatically incremented for each new row, giving each row a unique integer ID. It is as the primary key of the table, meaning that is the unique value used to identify each row in the table.
• full_name: A column of the varchar data type, with a maximum length of 50 characters. A NOT NULL constraint is added to ensure that it cannot be empty.
• email_address: A column of the varchar data type, with a maximum length of 50 characters. A UNIQUE constraint ensures that there are no duplicate email addresses in the table.
• hourly_wage: A column of decimal data type, with a precision of 10 and a scale of 2. A CHECK constraint ensures that the hourly_wage value is greater than 0.

There are many built in data types available in PostgreSQL, and additional constraints can be used to ensure the validity of data. You will need to decide which to use based on the objects you wish to represent in your database.

Once the above statement has been executed the staff table will be created containing the columns specified. Data can then be inserted and queried.