All possible ways you can Insert Data in Postgres

Postgres INSERT

Syntax

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.

Examples

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);
INSERT INTO products DEFAULT VALUES;

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.

Example

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

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) 
UPDATE
	  SET product_no = EXCLUDED.name;

Share this post