Yogesh Chauhan's Blog

All possible ways you can Insert Data in Postgres

in Postgres on June 12, 2020

Postgres INSERT


INSERT INTO table_name(column_1, column_2, …)
VALUES (value_1, value_2, …);

For multiple rows all you need to do is attach the VALUES code chunk. Add additional comma-separated value lists after the first list, each value in the list is separated by a comma (,).

Like this

INSERT INTO table_name(column_1, column_2, …)
VALUES (value_1, value_2, …)
VALUES (value_1, value_2, …)
VALUES (value_1, value_2, …)

From data from another table, do something like this:

INSERT INTO table_1_name(column_1,column_2,...)
SELECT column_1,column_2,...
FROM table_2_name
WHERE condition;

The number of columns needs to be same while copying the data.


When a table is created, it contains no data. The first thing to do before a database can be of much use is to insert data.

Data is conceptually inserted one row at a time. Of course you can also insert more than one row, but there is no way to insert less than one row.

Even if you know only some column values, a complete row must be created.

To create a new row, use the INSERT command. The command requires the table name and column values.

For example, let's create a new table:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric

An example command to insert a row would be:

INSERT INTO products VALUES (1, 'Cheese', 9.99);

The data values are listed in the order in which the columns appear in the table, separated by commas.

Usually, the data values will be literals (constants), but scalar expressions are also allowed.

The above syntax has the drawback that you need to know the order of the columns in the table.

To avoid this you can also list the columns explicitly.

For example, both of the following commands have the same effect as the one above:

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

Many users consider it good practice to always list the column names. Just like we saw in the syntaxes above.

If you don't have values for all the columns, you can omit some of them. In that case, the columns will be filled with their default values.

For example:

INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');

The second form is a PostgreSQL extension. It fills the columns from the left with as many values as are given, and the rest will be defaulted.

For clarity, you can also request default values explicitly, for individual columns or for the entire row:

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);

You can insert multiple rows in a single command:

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

Tip: When inserting a lot of data at the same time, considering using the COPY command. It is not as flexible as the INSERT command, but is more efficient.

Upsert Using INSERT ON CONFLICT statement

It is basically update or insert = upsert.

Postgres will merge the row if it already exists otherwise it will add a new row. That is really helpful when trying to add valuable data using primary key.

ON CONFLICT clause is only available from PostgreSQL 9.5.

We need to add ON CONFLICT action to tell Postgres what to do if it finds similar row.

INSERT INTO table_name(column_1, column_2, ...) 
VALUES(value_1, value_2, ...)
ON CONFLICT target action;

As a target you can add 

1. column_name – any column name from the table.
2. ON CONSTRAINT constraint_name – name of the any UNIQUE constraint.
3. WHERE predicate – a WHERE clause with a predicate

In action you define 2 possible path. Do nothing or do something.

=> DO NOTHING if the rows exists.

=> DO UPDATE – update same row fields in the table.


INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99)
ON CONFLICT (product_no) 

Now, it won't do anything if the product_no exists in the table rather than giving an error.

We can add DO SOMETHING like this:

To update in case of conflict, we can access the current values by EXCLUDED.field and then use it to insert/update into the table.

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milkshake', 2.99)
ON CONFLICT (product_no) 
	  SET product_no = EXCLUDED.name;

Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
Essential SQL Commands We Need to KnowSQL/MySQLHow to add Date and Time picker in HTML and get the values using JavaScript?HTMLHow to reference an aliased column in the WHERE clause?SQL/MySQL10 Usability Blunders to AvoidUI/UXHow to convert an HTML radio buttons into a toggle switch using CSS?CSSCreate a responsive image gallery using CSS gridCSS