Postgres Create Tables

As we know, PostgreSQL is an open-source database management system that deals with enormous database operations efficiently. Previously, we discussed that PostgreSQL has many built-in functions and operations which you can perform accordingly. This article will practice some examples to create tables in PostgreSQL using different ways. We create tables for storing vast amounts of data in a structured form so that users can organize or update them as well when required. Companies with enlarging data use PostgreSQL for keeping data for its scalability, reliability, and security purposes. For creating a table, you need to perform a simple statement “CREATE TABLE” in PostgreSQL.

In the above syntax,

As the name suggests, “CREATE TABLE” creates the table in the database.

You can specify your table name in place of “table_name”.

First, you enter the column name in brackets with their specified datatypes, lengths, and column constraints. Column constraints are used to ensure that the column must follow the rule of data stored in a column. For example, if the column constraint is mentioned as the ‘Primary Key’ constraint, only that column is the ‘Primary Key’. Keep in mind that each column should be separated by a column when including a new column.

Lastly, mention the “table_constraints” as ‘Primary Key’, ‘Foreign Key’, and ‘Unique Key’.

Note that table constraints are applied to one or more columns, but column constraints are applied to their respective one column only.

Different ways to Create Table in PostgreSQL:

A table helps users to store, retrieve, update, delete or organize data in a structured form. You can easily and efficiently make changes and create tables in the database. Tables can be created in PostgreSQL using different techniques and ways; you can find suitable ways to create tables in this article.

1. Create Table using “CREATE TABLE” Statement in PostgreSQL:

Before, we have mentioned an introductory statement to create a table. Let’s create a table named “STUDENTS” this time using the “CREATE TABLE” statement.

In the above example, “Id” is the primary key and the NOT NULL constraint indicates that the “Id” cannot be NULL in the “STUDENTS” table. Similarly, “Name”, “course_name”, “course_no”, and “admission_on” are assigned with different datatypes with the constraint NOT NULL. The “email” column is assigned with the datatype VARCHAR with the length of 100 and UNIQUE constraint, which means there should not be repeated values in the “emails” column. On running the query, the following message is displayed in the console:

For confirmation, run the following query to make sure the “STUDENTS” table is created successfully, and you can view it with all the columns included.

The “Select” statement selects all the columns from the table mentioned, and it displays the result below:

You can see all the columns we have created earlier in the “CREATE TABLE” statement in the above results, which means the table was created successfully.

2. Using Temporary Table Statement to Create Table in PostgreSQL:

If a user wants to delete a table completely when shutting down the database, create a temporary table using the “Temporary Table” statement in PostgreSQL for using the table one time in a database session.

The above syntax will create a temporary table for your one-time use, and it will be deleted once the database session is ended. I have created a temporary table as an example for easier understanding.

The “Temporary Table” statement has created a table named “movie_premiere” with three different columns in it. The table will be removed once when the database is shut down. The created table is shown below:

This table shows all the temporary columns included ID, movie_name, and release_date, which we have created in a table “movie_premiere”.

3. Creating Table Using Like Option in PostgreSQL:

You have created a table already; now, you want to create another table same as the previous table, but without writing the statement all over. There is an easier way to create a similar table without hustling the need of writing it again; use the “LIKE” option to create the same table as the previous one. For copying the previous table and creating a new one, use this syntax:

The “LIKE” option copies all the attributes, including column names and their data types, like the previous table. I have named a new table “movie_premiere_extended” in the above syntax and copied it from the old table “movie_premiere” we created in the previous example. All the columns and datatypes of the table “movie_premiere” are now copied to the table “movie_premiere_extended”, it can be seen in the images below as well:

By running the “Select” statement, we can see that the table “movie_premiere_extended” shows the same column as the table “movie_premiere”.

Conclusion:

It is concluded that you can create a table easily using different ways; you just have to download a PostgreSQL on your device and start running the queries on it. We have learned three different ways in this guide to create a table, first using the “CREATE TABLE” statement, secondly using the “Temporary Table” statement, and lastly using the “LIKE” option to copy from the old table already generated simply. All three ways are efficient, but it is up to the user which way is the most suitable or convenient for him/her. The first method for creating a table is the most efficient as it does not require creating a table before, or once you have created the table, it will not be removed from the database when you end the session.