In this article, we will learn how to create a table in PostgreSQL. I am going to explain the following topics:

• The syntax of the CREATE TABLE statement and the options that we can use while creating it
• The column and table constraints are supported by PostgreSQL
• How to create a table with Primary key and foreign key
• Populate the list of the tables by querying the pg_admin schema and pgAdmin4 tool

Let us understand the CREATE TABLE statement.

The CREATE TABLE syntax and usage

We are using CREATE TABLE statement to create a table in the PostgreSQL database. Following is the syntax to create a new table.

In the syntax of CREATE TABLE statement,

1. table_name: Specify the name of the table after CREATE TABLE statement. The table name must be unique in the database. The query will return an error if the table with the same name exists on the database. We can use the IF NOT EXISTS to avoid such errors, this option allows us to create a table only when the table with the same name does not exist. If the table with the same name exists, it returns a notice and terminates the statement
2. col_name data_type (length): Specify the list of columns and their datatype, length, and constraint that you want to add to a table. The column must be separated by a comma
3. table_Constraint: Specify the table constraint that includes a primary key, foreign key, and CHECK constraint
4. tablespace_name: Specify the name of the tablespace. If you do not specify the tablespace, the table will be created in pg_default tablespace

PostgreSQL supports the following column constraints:

1. PRIMARY KEY: The primary key identifies a record from the table uniquely. A PostgreSQL table can have only one primary key. You can include multiple columns in one primary key
2. FOREIGN KEY: A foreign key establishes a Parent-child relationship between the tables of the PostgreSQL database. A foreign key on any table refers to the primary key of another table. The table whose column refers to the primary key column of another table is called the parent table and the table whose primary key column is being referred to is known as a child table
3. Not NULL: This constraint does not allow us to insert NULL value in the column on which it is created
4. UNIQUE: The UNIQUE constraint does not allow duplicate values to be inserted in the column
5. CHECK: This constraint checks the value being inserted in a table. The check constraint has expressions that evaluate against the data being inserted. If the condition evaluates TRUE, the data will be inserted in the column, or it will return an error

Example of CREATE TABLE statement

Let us understand how we can create a basic table. I am creating two tables named tblStudent, tblSchool and tblResult. The tblStudent table contains the following columns:

1. Student_ID: This column is used to save the student_id. The data type is an integer, and it is the primary key of the table, and the name of the primary key is PK_Student_ID
2. Student_Name: This column stores the name of the student. The data type is varchar, and the length is 500 characters. The column does not allow NULL values
3. Grade: This column stores the grade of the student. The data type of the column is character
4. recordAdded: This column stores the date and time when the record is inserted. A default value has been defined on the table. If the insert statement does not have a value of the recordAdded column, the query inserts the current date and time

The table is created in the pg_default tablespace, and the fill factor of the table is 70.

The following script is used to create a tblStudent table.

Screenshot:

Now, let us create another table named tblSchool. This table is used to store the data of the school. The tblSchool table contains the following columns.

1. School_id: This column stores the ID of the school. The datatype of the column is an integer. A primary key named pk_school_id has been created on the school_id
2. School_name: This column stores the name of the school. The data type of the column is varchar, and the length of the column is 500

The tblSchool table is created in the pg_default table space in fill factor is 90.

The following script is used to create a tblSchool table.

Screenshot

Let us create a table named tblResult. This table is used to store the result of the student. The details of the columns are as following:

1. Result_id: This column stores the ID for the result. The data type is an integer. This column is a primary key of the table
2. Student_ID: This column stores the ID of the student. The datatype of the column is an integer. It acts as a foreign key. The student_id references the primary key of the tblStudent table, which is pk_student_id. The foreign key rule for the UPDATE operation is CASCADE which means the changes will be applied on associated records of child tables. The rule for DELETE operation is RESTRICT on the tblStudent table, which means we cannot delete any record from the parent table
3. School_id: This column stores the ID of the school. The datatype of the column is an integer. This column acts as a foreign key. The school_id references the primary key of the tblSchool table, which is pk_school_id. The foreign key rule for the UPDATE operation is CASCADE, and the DELETE operation is RESTRICT
4. Marks: This column stores the total marks scored by the student. The datatype is float

The tblResult table is created in the pg_default table space in fill factor is 90.

The following script is used to create a tblResult table.

Screenshot

Once tables are created, let us view them.

View Tables in PostgreSQL

We can populate the list of tables created in the database by running the SELECT statement on the pg_tables. The pg_table is a system catalog view. It is in pg_catalog schema and provides the following information.

1. Schemaname: The name of the schema in which the table is created
2. TableName: Name of the table
3. Table owner: The owner of the table
4. Tablespace: The name of the tablespace in which the table is created
5. hasIndexes: It’s a Boolean value. If the table has any has an index, then it returns TRUE
6. Hasrules: It’s a Boolean value. If the table has any rules, then it returns TRUE
7. HasTiggers: It’s a Boolean value. If the table has any triggers, then it returns TRUE

Now, let us run the SELECT query to view the tables that we have created.

Query Output

As you can see, the tables tblStudent, tblSchool, and tblResult have been created.

Alternatively, you can view it from the pgadmin4 tool. Pgadmin4 is a web-based tool that is used to manage the PostgreSQL database. To view the tables created in DemoDatabase, Connect to PostgreSQL server 🡪 Expand Databases 🡪 Expand Demo Database 🡪 Expand Public 🡪 Expand Tables.

As you can see, these PostgreSQL tables are created.

Summary

In this article, we will learn how to create a table in PostgreSQL. I have explained the following topics:

• The CREATE TABLE syntax and the options that we can use while creating it
• The column and table constraints are supported by PostgreSQL
• How to create a table with Primary key and foreign key
• Populate the list of the tables by querying the pg_admin schema and pgAdmin4 tool

In my next articles, we will learn how to manage the tables in PostgreSQL.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
• Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022