What is PostgreSQL’s INSERT INTO?

PostgreSQL’s INSERT INTO is used to add one or more new rows to a table. When adding rows, you also need to add values for the corresponding columns as well.

What is the INSERT INTO command in PostgreSQL?

The INSERT INTO command allows you to add new rows to a table in PostgreSQL. You can either insert a single row or multiple rows at once. When using the INSERT command in PostgreSQL, you need to specify which columns you want to insert data into as well as the corresponding values.

What is the syntax for PostgreSQL’s INSERT command?

The basic syntax of INSERT INTO is as follows:

INSERT INTO table_name (column1, column2, column3, …, columnN)
VALUES (value1, value2, value3, …, valueN);

When using PostgreSQL’s INSERT INTO, you first need to identify the table where the rows should be added. Then, list the columns you want to update. If you’re adding values for all the columns in the table, you don’t have to specify the columns. Here’s what the syntax looks like:

INSERT INTO table_name
VALUES (value1, value2, value3, …, valueN);

The values must be entered in the correct order, matching the sequence of the table’s columns from left to right.

PostgreSQL INSERT INTO example

Here’s an example of how PostgreSQL INSERT INTO works in practice. First, we’re going to create a table named customer_list with four columns: id, name, city and address. Here’s how to do this:

CREATE TABLE customer_list(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
city VARCHAR(50),
address VARCHAR(255)
);

To add a row to this table, use the PostgreSQL INSERT INTO command as shown below:

INSERT INTO customer_list (id, name, city, address)
VALUES (1, 'Smith', 'London', '123 High Street');

In the next example, we don’t know the customer’s address, so we’re going to leave this field empty. The default value that is defined in the table will be used. If a default value hasn’t been set, NULL will be used. Here’s the code:

INSERT INTO customer_list (id, name, city)
VALUES (2, 'Johnson', 'Glasgow');

Adding multiple rows with PostgreSQL INSERT

PostgreSQL also allows you to add multiple rows at once using the INSERT command. Here’s how to insert two customers at the same time:

INSERT INTO customer_list (id, name, city, address)
VALUES 
(3, 'Williams', 'Cardiff', '45 Oak Avenue'), 
(4, 'Brown', 'Southampton', '78 Pine Road');

Each row is enclosed in brackets and separated by commas.