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. 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 to 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:

- Firstly, specifies the INSERT INTO command to insert a specific record into the targeted table.

- tab_name is the name of the targeted table, 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.

Example # 1: 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;

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 been inserted successfully 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. On successful execution of the select command, you will get the following output:

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

Example # 2: How to Insert Several 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.

What does the RETURNING clause do 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 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 to use the RETURNING clause in PostgreSQL?

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 need to 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:

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.

Conclusion

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 explained the working of the INSERT query with the help of suitable examples.