PostgreSQL Insert Query

Insert query used to populate a table with rows.

Note that PostgreSQL Insert statement is ANSI-compliant

To learn about Insert we need to know about Tables and how to create them. Please read post Create Table

There different ways we can use insert statements

  1. Insert without specifying columns
  2. Insert with specifying columns
  3. Insert multiple rows
  4. Insert using select statement

Consider below table “orders”

create table orders(orderid int,orderdate date,orderitem varchar(50),orderamount float);

postgres=# create table orders
(
orderid int not null,
orderdate date,
orderitem varchar(50),
orderamount float
);                          
CREATE TABLE

postgres=# \d orders

Table "public.orders"
Column      |         Type          | Null orderid     | integer               | No        
orderdate   | date                  |           
orderitem   | character varying(50) |           
orderamount | double precision   

Orders table is freshly created and it has no data, let try to insert a single row.

Insert without specifying columns

In this way we no need specify the column names, insert query will identify columns automatically with the order of the values in the statement.

insert into orders values(1,current_date,’Laptop’,38787.75);

Here it assume value 1 for orderid, current_date for orderdate etc.

postgres=# insert into orders values(1,current_date,'Laptop',38787.75);
INSERT 0 1

postgres=# select * from orders where orderid = 1;
orderid|orderdate|orderitem| orderamount 
1 | 2022-08-26 | Laptop|38787.75
(1 row)

Note that all the columns except orderid are nullable ( i.e null can be accepted ) so we can ignore these columns too and it will insert a row with only oderid.

insert into orders values(2);

postgres=# insert into orders values(2);
INSERT 0 1

postgres=# select * from orders where orderid = 2;
 orderid | orderdate | orderitem | orderamount 
 2 |      |        |            
(1 row)

Insert with specifying columns

Insert can be done with specifying the columns.

insert into orders(orderid, orderdate, orderitem, orderamount) values(3, current_date, ‘Desktop’, 26000.00);

postgres=# insert into orders(orderid,orderdate,orderitem,orderamount) values(3,current_date,'Desktop',26000.00);
INSERT 0 1

postgres=# select * from orders where orderid = 3;
 orderid | orderdate  | orderitem | orderamount 
---------+------------+-----------+---
3 | 2022-08-26 | Desktop|  26000
(1 row)

postgres=# insert into orders(orderid,orderdate) values(4,current_date);
INSERT 0 1

postgres=# select * from orders where orderid = 4;
 orderid | orderdate  | orderitem | orderamount 
--+------------+-----------+--
4 | 2022-08-26 |           |            
(1 row)

Insert multiple rows

We can insert multiple rows in one statement, see below example

postgres=# insert into orders(orderid,orderdate,orderitem,orderamount) 
values (5,current_date,'Mouse',300.00),    (6,current_date,'Keyboard',500.00),    (7,current_date,'headphone',2500.00)
;
INSERT 0 3

postgres=# select * from orders where orderid in (5,6,7);
orderid | orderdate  | orderitem | orderamount 
---------+------------+-----------+--
5 | 2022-08-26 | Mouse  |         300
6 | 2022-08-26 | Keyboard|        500
7 | 2022-08-26 | headphone|       2500
(3 rows)

Insert using select query

We can construct an select sql query and use it with insert statement

postgres=# insert into orders(orderid,orderdate,orderitem,orderamount) 
select 8,current_date,'RAM',56000.00;
INSERT 0 1

postgres=# select * from orders where orderid = 8;
 orderid | orderdate  | orderitem | orderamount 
---------+------------+-----------+--------
 8 | 2022-08-26 | RAM       |       56000
(1 row)

If you have similar table structure you can create insert from another table select query

postgres=# create table orderhistory
(
orderid int not null,
orderdate date,
orderitem varchar(50),
orderamount float
);   
CREATE TABLE

postgres=# insert into orderhistory select * from orders;
INSERT 0 8

postgres=# select * from orderhistory;
 orderid | orderdate  | orderitem | orderamount 
---------+------------+-----------+--
1 | 2022-08-26 | Laptop|    38787.75
2 |            |       |            
3 | 2022-08-26 | Desktop|    26000
4 | 2022-08-26 |        |        
5 | 2022-08-26 | Mouse  |    300
6 | 2022-08-26 | Keyboard|   500
7 | 2022-08-26 | headphone|  2500
8 | 2022-08-26 | RAM    |    56000
(8 rows)