PostgreSQL INSERT: Inserting Data into a Table

PostgreSQL INSERT statement

In PostgreSQL, the insert statement helps insert a new row or row into the table. You can insert rows specified by value expressions, zero, or multiple rows resulting from the query.

Syntax of PostgreSQL INSERT INTO

INSERT INTO TABLE_NAME (column1, column2, ...columnN)
VALUES (value1, value2,...valueN);

You can see in the above syntax column 1 to N are the names of the columns in the table into which you wanted to insert data. The target column needs to list in any specific order. The values supplied by the query or values clause is either the corresponding values for the columns.

Once the query is executed, you can see the output message.

Insert oid 1

This output message will be displayed if only a single row is inserted, oid is the numeric OID assigned to the inserted row.

Example of PostgreSQL Insert into Table

Consider the following table, “tutorials,” with two columns.

“id” integer DEFAULT value 1
“tutorial_name” text DEFAULT value postgre
And no constraints

Here, are steps for PostgreSQL insert into table:

Step 1) Inserting a row into

INSERT INTO tutorials(id, tutorial_name) VALUES (1, 'postgre');

NOTE: Only the characters or date values need to be enclosed with single quotes when inserting a row.

Step 2) However, If you insert data into all the columns, you can omit the column names. The same insert statement can also be written as,

INSERT INTO tutorials VALUES (1, 'postgre');

Step 3) The data values are listed in the order as the columns appear in the table, separated by commas.

The above syntax has an issue which you need to know the order of the columns in the table. To overcome this problem, you can also list the columns explicitly.

For example, both below-given commands have the same effect as displayed below:

INSERT INTO tutorials(id, tutorial_name) VALUES (1, 'postgre');
INSERT INTO tutorials(tutorial_name, id) VALUES ('postgre',1);

Step 4) In this example, you can see that if you do not have values for all the columns, you can omit some of them.

In that case, the columns will be automatically filled with their default values if specified.

INSERT INTO tutorials(id) VALUES (5);

Step 5) You can also request default values for individual columns or the entire row:

INSERT INTO tutorials(id, tutorial_name) VALUES (1, DEFAULT);
INSERT INTO tutorials DEFAULT VALUES;

Step 6) You can multiple rows with the just single command:

INSERT INTO tutorials(id, tutorial_name) VALUES
(1, 'postgre'),
(2, 'oracle'),
(3, 'mysql'),
(4, 'mongo');

Step 7) In PostgreSQL, it is also to insert the result of a query which might be no rows, one row, or multiple rows:

INSERT INTO tutorials (id, tutorial_name)
SELECT id, tutorial_name FROM tutorials
WHERE tutorial_name = 'mysql';

PostgreSQL Insert statement using pgAdmin

Here, are steps to Insert statement using pgAdmin in Postgre SQL

Step 1) In the object tree

1. Right Click on the table where you want to insert data
2. Select Scripts
3. INSERT Script

Step 2) In the Insert Panel

1. Edit the Query
2. Click the lighting button
3. Observe the output

Step 3) You can also goto to Tools > Query Tools to open the Query Editor, but you will not get the default insert query.

Summary

Code/Parameter/Tip	Description
INSERT INTO TABLE_NAME (column1, column2, …columnN) VALUES (value1, value2,…valueN);	Used to insert values into a table
INSERT INTO tutorials(id, tutorial_name) VALUES (1, ‘postgre’); INSERT INTO tutorials(tutorial_name, id) VALUES (‘postgre’,1);	Order of columns is not important. Both will have the same effect.
INSERT INTO tutorials VALUES (1, ‘postgre’);	If you are providing values for all columns, no need to specify
INSERT INTO tutorials (id, tutorial_name) SELECT id, tutorial_name FROM tutorials WHERE tutorial_name = ‘mysql’;	You can specify single or multiple subqueries referenced by name in the INSERT query.
DEFAULT VALUES	Use of this clause to fill all columns with their default values.
expression	A value and expression which should be assigned to the corresponding column.
omeDEFAULT	The default clause is used when the corresponding column is filled with its default value.