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 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to change the style for checked radio button or checkboxes using CSS?CSSThe 7 Security Objectives of Any Organization for IT and Network SecurityMiscellaneousWhat’s a page re-rendering in React?ReactWordPress: How to display slider repeater fields in ACF?WordPressConfiguring Modules with @forward rule in SCSS (Sass)SCSSConditional operator in JavaScript (aka ternary operator)JavaScript