PostgreSQL is an open-source commonly used relational database system that stores data in tables. The tables contain columns and rows to store data in a structured way. We can perform certain operations on the table like getting data from the table, updating data in the table, adding columns to the table, etc. to perform different operations on the table. But before that, first, we have to create the table.
In this post, we will learn to create a table in Postgres. The content of the article will be:
Let’s get started with the article.
How to Create a Table in Postgres Using psql?
We can create a table in psql simply by querying the CREATE statement for the table. The basic/fundamental syntax used to create a table is:
CREATE TABLE tabName(
col1_name dtype,
col2_name dtype,
col3_name dtype,
.....
colN_name dtype,
);
Syntax Description
- The statement CREATE TABLE is used to create a table.
- After the CREATE TABLE statement, we have to write the name we want to create a table with.
- In the parentheses/brackets, we need to write the column names with their data types like int, text, char, etc.
- A comma separates all the column names.
There are some commonly used parameters that are accepted by the CREATE TABLE statement. These parameters are:
- temp/Temporary – This parameter will create a temporary table.
- If not exist – This will always return a notice rather than returning an error.
- Logged – This parameter will return an unlogged table.
We can create a table in any database. By default, the created database is “postgres”. You can create the table in the “postgres” database or else you can also create a new database and create a table in it. Now we will try creating a table in a database using psql.
Example: Create a Table in a Database
Perform the below-given steps to create a table in a specific database.
Step 1: Connect to an Existing Database
To create a table in the database, you will have to get connected to the existing database first. Let’s consider that we want to create a table in the database named “test_database” which already exists in my system. Now to get connected to the “test_database” we will write the following command in the psql.
\c test_database
This will return the following output:
The “test_database=#” instead of “postgres=#” ensures that we have been successfully connected to the “test_database=#”.
Step 2: Create a Table
The Very next step is to make/create a table. The table can be created by using the same syntax given above. Suppose we want to create a table named ”fee_invoice” with columns invoice_no having data type integer and the other column as status having data type Text. The query can be written as:
CREATE TABLE fee_invoice( role_id INT, role_name TEXT);
The query will result in the successful creation of a table named fee_invoice which can be verified from the following output:
The “CREATE TABLE” ensures the creation of the table. There is another way to know whether the Postgres table with the “fee_invoice” name has been created or not.
Step 3: Verify the Creation of a Table Using \d Command
The \d command is used to list all the tables present in the currently connected database. Let’s execute this command and see the output:
\d
We can see that the table created is displayed in the list of relations of the test_database.
How to Create a Table in PostgreSQL Using pgAdmin?
A table can also be created in pgAdmin as well. There are two methods to create a table in pgAdmin these are:
- Creating a table using Postgres queries in pgAdmin
- Creating a table manually in pgAdmin
Let’s see how we can create a table by both methods in pgAdmin.
Method 1: Creating a Table Using Postgres Queries in pgAdmin
We can create a table in pgAdmin using the Postgres queries the same as we did in psql. For that, open the pgAdmin and enter your password then Follow the below-given steps to create a Postgres table using queries in pgAdmin.
Step 1: Select the Database and Open the Query Tool
From the side panel, first, click on the database in which you want to create a table, then open the query tool from the top query tool icon or by selecting this option from the drop-down menu appearing after right-clicking on the database like this:
This will open the query tool for that database. we can create a table in that database by writing the query now.
Step 2: Create a Table
Suppose we want to write a query to create a table in the test_database. The table is named “acc_details”. The table has columns; userName with a data type of Text and ph_number having an integer data type. The query for the table can be written as:
CREATE TABLE acc_details(
UserName TEXT,
ph_number INT);
The above query will successfully create a table. The following output can ensure this:
This verifies the creation of the acc_details table. However, there is another way we can verify the creation of the table.
Step 3: Verify the Creation of a Table
We can verify the creation of the table from the side panel by expanding the option of that particular database. In this case, expand the “test_database”, then go to the “schemas” and then “tables”. By expanding the “tables” option, you will get to see your created table.
Hence, the table has been created in Postgres. This is how we can create a Postgres table in pgAdmin.
Method 2: Create a Table Manually in pgAdmin
Now we will see how can we create a database table manually, by following the steps given below:
Step 1: Select the Database
First, select the database in which you want to create the table. For instance, I have selected the “test_database” in this case.
Step 2: Select the Schema
After selecting and expanding the database, select the schema to be “Public” by expanding the “schemas” option.
Step 3: Create a Table
Now right-click on the public, and a drop-down menu will appear. Hover on the “create” and this will lead to another drop-down menu from there select “table” like this.
Clicking on the “Table…” option will open another window for you to ask for the table details. Write the table name you want to make/create in the database.
Move to the “Columns” tab to specify the columns. We can add more rows by pressing the “+” sign like this:
We need to fill out the fields after adding a row with the name of the column, its datatype, and its other attributes. We can make a column a primary key by toggling on the “primary Key?” attribute.
After adding another column “Smarks” with an integer datatype, we can get the respective generated query in the “SQL” tab like this:
Now press the ”save” button to create the table.
Step 4: Verify the Table Creation
After saving the table, we will be able to see the created table under the table options in that specific database like this:
In this way, you can manually create a table in pgAdmin using Postgres.
Conclusion
We can create the Postgres table by executing the CREATE TABLE statement in psql or pgAdmin. However, we can create the table manually in pgAdmin. Creating a table is important for inserting data and performing different operations on the table and the inserted data. In this tutorial, we have addressed and learned to create a table in Postgres using psql and pgAdmin.
