PostgreSQL offers a convenient statement named “INSERT” that is used to insert/add new records in a table. Using the INSERT query, an individual or several records can be added to the selected table. It allows users to insert data of any valid data type into a table easily. Later on, different commands and queries can be used to perform any specific operations on this inserted data.

Quick Outline

This Postgres blog will walk you through the following content regarding the Postgres INSERT query:

• How to Use the INSERT Query in Postgres
• How to Insert a Single Row to a Table in PostgreSQL
• How to Insert Several/Multiple Rows to a Table in PostgreSQL
• How to Use the SELECT Query With the RETURNING Clause in PostgreSQL
• How to Insert a Date Value to a Column in PostgreSQL
• How to Insert a Row Into a Postgres Table Using pgAdmin | GUI Method
• Final Thoughts

So, let’s learn how to use INSERT Query in PostgreSQL with the help of some practical examples.

How to Use the INSERT Query in Postgres

As we have discussed earlier, the INSERT command/statement is used to insert new records in any specific table. Let’s consider the below-listed points for a profound understanding of the INSERT query:

• The character data must be enclosed in single quotes (‘), such as 'INSERT QUERY'.
• Use the “YYYY-MM-DD” format to insert a date into a table.
• In PostgreSQL, omitting the mandatory columns in the INSERT command will throw an error.
• Postgres will utilize the column’s default value if someone skips the optional columns.
• Use the Keyword “DEFAULT VALUES” to insert default values into the columns.

Following will be the basic syntax of the INSERT INTO command in PostgreSQL:

INSERT INTO tab_name (col_1, col_2, col_3,...col_N)
VALUES (val_1, val_2, val_3,...val_N);

The above snippet utilizes the following standards:

• First, it specifies the INSERT INTO command to insert a specific record into the targeted table.
• tab_name is the name of the targeted table while col_1, col_2, col_3, . . . , col_N are the column names.
• VALUES is a clause used between the column names and an ordered list of comma-separated values.

All in all, the INSERT INTO command takes a list of column names and values in a specific order and inserts them into the selected table. There must be the same order of the column names and values.

How to Insert a Single Row to a Table in PostgreSQL

Follow the below-given step-by-step guidelines to insert data into a specific table in Postgres:

Step 1: Select a Table Using \dt Command

Firstly, select a table where you want to insert the data. To do so, run the “\dt” command:

\dt;

Let’s say we need to insert data into the “team_info” table.

Step 2: Describe the Selected Table Using \d Command

Type the “\d” command followed by the table name, i.e., “team_info”, and hit the enter button to illustrate the table details:

\d   team_info;

To avoid errors, keep this table structure in mind when inserting data into the selected table.

Step 3: Run the INSERT Query

Let’s execute the INSERT INTO command to insert some data into the “team_info” table:

INSERT INTO team_info (team_id, team_name, team_ranking)
VALUES (1, 'Pakistan', 1);

The output shows that one row has successfully been inserted into the team_info table.

Step 4: Verify the Inserted Data Using Select Command

Execute the SELECT query to check whether the data has been inserted into the selected table or not:

SELECT * FROM team_info;

The “SELECT *” command will fetch all the column’s data, as shown in the following output:

The output authenticates that the data has been inserted into the selected table.

How to Insert Several/Multiple Rows to a Table in PostgreSQL

In this example, we will use the comma-separated syntax to insert multiple rows in the “team_info” table using the “INSERT” query:

Step 1: Execute the INSERT Query

Run the INSERT INTO statement to insert some data into the “team_info” table:

INSERT INTO team_info (team_id, team_name, team_ranking)
 VALUES 
   (2, 'England', 4),
   (3, 'Australia', 2),
   (4, 'South Africa', 5),
   (5, 'Srilanka', 3);

In the above snippet, we utilized the INSERT INTO query to insert four rows into the team_info table.

Step 2: Verify the Inserted Data Using Select Command

Use the SELECT command to verify whether the data has been added to the selected table or not:

SELECT * FROM team_info;

The output shows that the data has been inserted into the selected table successfully.

How to Use the INSERT Query With the RETURNING Clause in PostgreSQL

Use the RETURNING clause with the “INSERT INTO” command to get the last entered/inserted ID from the selected table.

Here is the basic syntax of the RETURNING clause of the INSERT statement in Postgres:

INSERT INTO tab_name (col_1, col_2, col_3,...col_N)
VALUES (val_1, val_2, val_3,...val_N),
RETURNING id;

The above-given query will insert the values into the specified columns and will return the last inserted ID.

Example: How Does the RETURNING Clause Work With INSERT Statement?

Let’s run the RETURNING clause with the collaboration of the INSERT INTO statement to get the last inserted ID:

INSERT INTO team_info (team_id, team_name, team_ranking)
VALUES (6, 'West Indies', 7)
RETURNING team_id;

The output shows that the RETURNING clause returns the last entered ID:

How to Insert a Date Value to a Column in PostgreSQL

Postgres allows us to insert a date into a column using the DATE type. To do this, we must follow the Year-month-day format, i.e., “YYYY-MM-DD”.

Example: How to Insert a Date to a Specific Table?

In this example, we will insert a date value to a particular table, i.e., “article_details”:

Step 1: Insert Date Value into a Table Using INSERT Query

Run the INSERT query to insert the date value into the “article_details” table:

INSERT INTO article_details (article_id, article_title, published_date)
VALUES (1, 'Postgres   INSERT Query', '2022-07-22');

Step 2: Verify the Working of INSERT Query Using SELECT Command

Let's run the "SELECT" query to see if the specified date has been inserted into the table:

SELECT * FROM article_details;

The output verifies that the “date” value has been entered into the “article_details” table.

How to Insert a Row Into a Postgres Table Using pgAdmin? | GUI Method

pgAdmin is a GUI-based management tool for Postgres that can be used to perform database operations effectively. It allows us to execute the SQL commands and queries to perform different database operations. It also allows us to use its graphical interface to perform various database operations, such as inserting new rows into a table.

Expand the database in which the desired table resides, select the “public” schema, and then navigate to the “tables” section to select the desired table:

Select the table of your choice, right-click on it, hover over “View/Edit Data”, and select the “All rows” option:

From the popup output, select the below-highlighted “add row” option to insert a new record/row into the table:

Double-click on the field in which you want to insert data, and then click on the “save data changes” option to store the data in the selected table:

That’s all about inserting data into a Postgres table using psql and pgAdmin.

Final Thoughts

In PostgreSQL, the “INSERT” statement is used to insert/add new records in a particular table. The INSERT query inserts an individual or several records into the selected table. In Postgres, the RETURNING clause is used with the collaboration of the INSERT statement to get the last entered/inserted ID from the selected table. This post has explained the workings of the INSERT query with the help of suitable examples.