PostgreSQL CREATE TABLE – How to Create a Table in PostgreSQL?

By /
Create A Table In Postgresql

Creating a table is a basic functionality in any database after creating a database. In this tutorial, we will learn to create a table in PostgreSQL with multiple points related to it. So, let’s get started!

Also Read: How to Create a Database in PostgreSQL

Introduction to PostgreSQL CREATE TABLE

There are a few things you need to learn first before learning to create a table in any DBMS. First, you should know the datatypes, their features and limitations, the basic syntax which is almost similar in all DBMSes, database engines(little information will do the work), and of course, a semicolon.

A tutorial on PostgreSQL datatypes is a great start to learning about the available datatypes.

Now let’s see the syntax of creating a table in PostgreSQL.

Syntax of PostgreSQL CREATE TABLE

The syntax of creating a table in Postgresql is as follows:

CREATE TABLE table_name(
col_name1 datatype[(range)] [column_constraint], -- this is comment
col_name2 datatype[(range)]
) [INHERITS existing_table_name];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

  • col_name: A column name as per the naming convention
  • datatype: Any available datatype with a valid range.
  • column_constraint: PostgreSQL column constraint such as primary key, unique, not null etc.
  • –: (two hyphens)The comment after the comma
  • INHERITS: It will inherit all the table columns from the existing table (optional)

Note that, you can write a query in a single line as well as in multiple lines. You are free to use white spaces, tab spaces and new lines. It automatically recognises the new line and doesn’t terminate the statement until you use the semicolon.

Also, it is case-insensitive in terms of keywords and identifiers except when the identifiers are specified in double-quotes.

Now let’s create a table using the above syntax to store the details of students.

Create a Table in PostgreSQL

Let’s create a table with columns auto-incrementing id, name, and age.

CREATE TABLE students(
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the id column is of type serial having a constraint primary key. It is similar to the AUTO_INCREMENT in MySQL which automatically assigns the positive integer numbers to a record starting from 1. The name column is of varchar type having a length limit of 100 characters. The name column must contain a value while inserting or updating a record as we have explicitly mentioned the not null constraint. The third column age is of type integer and can be empty.

Create Table Students
Create Table Students

As you can see, the table was created successfully.

Creating a Foreign Key on a Table

Now let’s say how we can create a foreign key in PostgreSQL.

Here, we will create a table ‘semMarks’ to set the marks of each student.

CREATE TABLE semMarks(
id SERIAL PRIMARY KEY,
studId INT NOT NULL,
semester INT NOT NULL,
marks INT NOT NULL,
CONSTRAINT studentSemMarks
FOREIGN KEY(studId)
REFERENCES students(id)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have used the serial id of a student record as a foreign key in the semMarks table. Just think about it as we are linking two tables through the student ID and that will be determined by the REFERENCES keyword where we have mentioned the table name and the column name of the parent table.

Create Table Semmarks
Create Table Semmarks

As you can see, our table was created successfully.

Now, while inserting a record in the semMarks table, ensure that the studId must is present in the students table because the semMarks will act as a child table and a child table can’t hold a record if there is no record present in the parent table.

Let’s try it by inserting a record in the semMarks table.

INSERT INTO semMarks(studId, semester,marks) 
VALUES(1,1,300);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, studId 1 is not present in the students table. Therefore, PostgreSQL will throw an error.

Foreign Key Error
Foreign Key Error

As you can see, we got an error that studId is not present in the students table.

We will learn more about the foreign key in PostgreSQL later in another tutorial.

Conclusion

In this tutorial, we have learned to create a table in an easy way in PostgreSQL. It is very easy to create a simple table. However, to reduce the complexities such as time and space, you should have knowledge about the primary key, foreign keys and other stuff which you will learn with time.

Reference

PostgreSQL official documentation