PostgreSQL Insert

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 at a time. Even if you know only some column values, a complete row must be created.
Basic Synatx:

INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

–To create a new table for use the INSERT command

postgres=# CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

–An example command to insert a row would be:

postgres=# INSERT INTO products VALUES (1, 'Cheese', 100);
INSERT 0 1

–view the products table for successfully inserted or not

postgres=# select * from products;
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |   100
(1 row)

Both of the following commands have the same effect as the one below,Many users consider it good practice to always list the column names.

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

postgres=# select * from products;                                                   
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |   100
          1 | Cheese |  9.99
          1 | Cheese |  9.99
(3 rows

–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,

postgres=#
INSERT INTO products (product_no, name) VALUES (2, 'bargar');
INSERT INTO products VALUES (3, 'juice');

— discribe the inserted values

postgres=# select * from products;                                      
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |   100
          1 | Cheese |  9.99
          1 | Cheese |  9.99
          2 | bargar |      
          3 | juice  |      
(5 rows)

–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 (4, 'cake', DEFAULT);
INSERT INTO products DEFAULT VALUES;

postgres=# select * from products;                                                    
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |   100
          1 | Cheese |  9.99
          1 | Cheese |  9.99
          2 | bargar |      
          3 | juice  |      
          4 | cake   |      
            |        |

–This example inserts some rows into table “products” from a table “Item” with the same column layout as Products:
–item table list

postgres=# select * from item;
 item_no |  name  | cost 
---------+--------+------
       1 | laptop |   20
       2 | pen    |   10
       3 | paper  |    8
(3 rows)

–Now insert into products from item table

postgres=# INSERT INTO products SELECT * FROM Item WHERE cost > 8;
INSERT 0 2

postgres=# select * from products;                                
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |   100
          1 | Cheese |  9.99
          1 | Cheese |  9.99
          2 | bargar |      
          3 | juice  |      
          4 | cake   |      
            |        |      
          1 | laptop |    20
          2 | pen    |    10
(9 rows)
postgres=# \d demo
             Table "benz2.demo"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(20) |

–insert multiple values in a single command

postgres=# INSERT INTO products (product_no, name, price) VALUES
    (5, 'nijam', 1000),
    (6, 'benz', 6000),
    (7, 'jam', 3456);

postgres=# select * from products;                                
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |   100
          1 | Cheese |  9.99
          1 | Cheese |  9.99
          2 | bargar |      
          3 | juice  |      
          4 | cake   |      
            |        |      
          1 | laptop |    20
          2 | pen    |    10
          5 | nijam  |   1000   
          6 | benz   |   6000
          7 | jam    |   3456 

(12 rows)

To do “bulk loads”, that is, inserting a lot of data, take a look at the COPY command. It is not as flexible as the INSERT command