PostgreSQL INSERT – How to Insert New Rows into a Table

PostgreSQL stores data in tables. That data is inserted in the table so that we can perform effective operations on the data. The data/values can be manually placed/inserted in a table using the pgAdmin. However, we can also insert data using the Postgres query. The INSERT statement inserts data values into a Postgres table. 

This write-up will teach how we can insert new rows into a table.

PostgreSQL INSERT – How to Insert New Rows into a Table?

The INSERT statement assists us in inserting rows in a Postgres table. The syntax for the INSERT statement is illustrated in the below box:

INSERT INTO t_name (col1, col2, col3, ... , coln)
VALUES
(col1_val_list),
(col2_val_list),
...
(coln_val_list);

Syntax Description

  • The INSERT INTO statement is used to insert values into the table.
  • The table name has to be written after the INSERT INTO statement. The list of column names of that specific table is passed in the statement.
  • The VALUES clause is used to declare the values to add/insert in the Postgres table.
  • After this VALUES clause, we provide the list of values that to insert/add to the Postgres table.

 Let’s see how the INSERT statement inserts values in the table with the help of an example.

Example 1: Inserting a Row into a Table

To insert the values in a table, we first need to create a database table.

Step 1: Creating a Table

We will first create the table named “messages” in the database using the following query.

CREATE TABLE messages (
    msg_id serial PRIMARY KEY,
    msg_content VARCHAR (255) NOT NULL,
    msg_status VARCHAR (255) NOT NULL,
  sent_to VARCHAR (255) NOT NULL
);

The above query will create a table in the database having their respective data types declared above. 

We can also see the table structure using the SELECT query like this:

SELECT * FROM messages;

The table looks like this:

A table is created with no rows as no data/ row is inserted in the table. Now, we may insert rows into the created Postgres table.

Step 2: Insert values in the Table

To insert a row into the table, we will be using the INSERT INTO and the VALUES statements as used in the general syntax. Following will be the query to insert a row into a table:

INSERT INTO messages( msg_content, msg_status, sent_to)
VALUES('Hello!','Read','Charlie');

The above query inserts values into the table. As the msg_id is declared a serial it will automatically generate a series of numbers for each row. We don’t need to insert any value into the table. The above query says that:

  • The values will be inserted into the “messages” table.
  • The column names of the “messages” table are specified in parentheses.
  • Following the VALUES clause, the values are written.

The above query will return the following output:

This shows that the values are successfully inserted into the table.

To see the table data, we may again execute the SELECT query.

SELECT * FROM messages;

We can see that a row is inserted into the table and similarly, we can insert multiple rows into the Postgres table.

Example 2: Inserting Multiple Rows into a Table

We can also add/insert multiple rows in a table at the same time. This is done similarly as we insert a single row. The query can be written as:

INSERT INTO messages( msg_content, msg_status, sent_to)
VALUES('This is Kate!','Read','Charlie'),
      ('Can you send me the finalized report?','Delivered','Charlie'),
      ('I need to get it approved','Delivered','Charlie');

The above query will insert the values specified. The output of the above query will be:

We will now see how the whole table looks like by running the SELECT query:

We can see that 3 more rows have been inserted into the table. So this is how we insert/add data rows in a Postgres table.

Conclusion

To insert values/rows in a Postgres table, the INSERT INTO clause is used. The VALUES clause is also used to write the values to be inserted. In this post, we learned to insert a row into a table or insert multiple rows into a Postgres table at the same time by using the INSERT INTO clause.

Join 10 Select Businesses Growing with Us! 500M+ Views Across Our Blogs & YouTube Channels – Be Part of Our Success. Click here to learn more. Click here if you are interested. ×