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.
