Postgres Insert Into Table Examples

In PostgreSQL, you have created a table with various columns in it. Now, you need to insert data into those columns. There raises the question, how will you do that? Well, it’s not that difficult. If you follow this guide, most of your questions regarding inserting the data into the table will be answered adequately. This article will guide you on inserting values in a table by demonstrating several examples in PostgreSQL. The purpose of the “INSERT INTO” statement is to add a new row with the values of your database table. You can add single or multiple rows, as well using the “INSERT INTO” statement. Underneath is the basic syntax for the “INSERT” statement in PostgreSQL:

The above syntax states that:

• First, input the “INSERT INTO” command to insert the rows in the table.
• Then, enter the table name (table_name) in which you want to input the values along with the list of column names (column1, column2, … columnN) which should be separated by commas.
• Lastly, enter the “VALUES” (value1, value2, …, valueN) statement with similar data types as columns. In the list of values and columns, they should be in the same order.

Insert Into a Table With RETURNING Clause:

In the “INSERT” statement, you can also add the “RETURNING” clause if you want to return the information of rows specified. The subsequent statement can be used to return the information of the whole row inserted:

The asterisk “*” in the statement means return all the information of the row inserted.

Return a Specific Row:

You can also get the information of a specific row by specifying any column you want the information. For example:

In the above statement, I have specified the column name “username” with the “RETURNING” clause to get the information of that specific column in the results.

Return Using “AS” Keyword:

If you want to change the name of the returned value, you can use the “AS” keyword with the assigned name in the “RETURNING” clause:

The “AS” keyword in the previous statement will change the column name of “username” with “u_name” and return the name as “u_name” in the results.

PostgreSQL Insert Into Table Example:

Before getting into some “INSERT” statement examples, let’s create a table first to insert values in the table. We will only run the following statement to create a table in our database:

Now, the table name “PASSENGERS” is created, which is displayed as:

As we can see, the table values are empty. We will be inserting values in this table “PASSENGERS” using “INSERT” statements:

1. Inserting One Row Into a Table in PostgreSQL:

Let’s start with an easy example to insert a row in the table using the “INSERT” statement. The following statement will insert a row in the “PASSENGERS” table:

In the above syntax, after the “INSERT INTO” statement, the table name is specified as “passengers”, and all the columns are mentioned in the brackets () separated by commas. In “VALUES”, the data according to columns data types are specified. Take any column, for example; when we created the table, we specified the column “Id” as integer and “Name” column as Varchar. In the values column, I have written the integer and varchar values only. If we inserted ‘Jack’ in the place of the “Id” column, PostgreSQL would not have accepted it and shown an error. So, it’s important to write values and columns in an order with their specified data types. Keep in mind that the data types, including varchar or dates, need to be in single quotes. The output for the above statement looks like this:

For viewing your inserted values in a table, run this query:

This “Select” statement will select all the data of your table in the results as:

You can confirm that the values we inserted are now displayed in the table above.

2. Inserting Multiple Rows Into a Table in PostgreSQL:

As we added a single row in a table in the previous example, we can similarly insert multiple rows in a table using the following statements:

In this statement, add different values followed by commas after each value to insert values in the “passengers” table. The above statement to add multiple rows into a table gives the following results:

To confirm the values are inserted in the table, run the following query:

By running the “Select” statement, you can view the output and verify the values you have inserted:

All the values we have inserted in the table using the “INSERT” statement are updated and displayed in the output above.

3. Insert Using Default Values Keyword in PostgreSQL:

The “Default” keyword will input null values by default in PostgreSQL. The following statement demonstrates the results:

The “Default” keyword will add the null values in columns “Payment” and “Travel_date”, the output after displaying the table will be:

In the above output, “Payment” and “Travel_date” columns are assigned to a null value by using the “Default” keyword.

Note, the “Default” keyword would be functional if you assign it to the column that accepts null values.

Conclusion:

In this article, we have learned through various examples of insert values into a table in PostgreSQL. We have learned to insert single and multiple values in a table, insert values using the “Default” keyword, and the “Returning” clause. All the examples are effective to implement. PostgreSQL allows you to use the “INSERT” keyword in queries making it less complicated to insert values in a table. We hope you found this article helpful. Follow Linux Hint for more tips and information.