CREATE TABLE - (PostgreSQL Table)

In PostgreSQL, the CREATE TABLE statement is used to create a new table in the database. Tables are used to store data in a structured way, with each table consisting of multiple rows and columns. In this tutorial, we will discuss how to use the CREATE TABLE statement in PostgreSQL to create a new table.

Syntax

The basic syntax for creating a new table is as follows:

CREATE TABLE table_name (
    column_1 data_type,
    column_2 data_type,
    ...
    column_n data_type
);

Here, table_name is the name of the new table to be created, and column_1 through column_n are the names of the columns in the new table. For each column, we must specify the data type that it will store.

Example

Let's say we want to create a new table named users, with columns for id, name, and email. We could use the following CREATE TABLE statement:

CREATE TABLE users (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL,
    email varchar(100) UNIQUE NOT NULL
);

Here, we have created a users table with three columns:

• id: a serial column that serves as the primary key for the table
• name: a varchar column that stores a user's name (maximum length of 50 characters), and is set to not allow NULL values
• email: a varchar column that stores a user's email address (maximum length of 100 characters), and is set to be unique (i.e. no two users can have the same email address), and also not allow NULL values

Explanation

In the above example, we have used the CREATE TABLE statement to create a new table named users. We have specified three columns for the table: id, name, and email.

We have used the serial data type for the id column, which automatically generates a new integer value for new rows and assigns this value to the id column. We have also used PRIMARY KEY to specify that this column is the primary key for the table.

For the name and email columns, we have used the varchar data type to store string data. We have also used the NOT NULL constraint to ensure that these columns are always populated with data.

Finally, we have used the UNIQUE constraint on the email column to ensure that no two users can have the same email address.

Use

The CREATE TABLE statement is one of the most frequently used statements in PostgreSQL. It is used to create new tables for storing data in a structured way that can be easily accessed and manipulated by other database objects, such as views, indexes, and triggers.

Important Points

• When creating a new table, we must specify the names and data types of each column in the table.
• We can use constraints to enforce certain rules on the data stored in the table, such as ensuring that certain columns are always populated, or that certain columns contain unique values.
• The serial data type is commonly used for creating auto-incrementing integer columns that serve as primary keys for the table.
• It is always a good practice to use the PRIMARY KEY constraint to specify the primary key column(s) for the table.

Summary

In this tutorial, we discussed the CREATE TABLE statement in PostgreSQL, which is used to create new tables for storing data in the database. We covered the syntax, example, output, explanation, use, and important points of the CREATE TABLE statement. With this knowledge, you can now create new tables in your PostgreSQL databases to store and manage your data.