Inserting Data

Data is inserted one row at a time. You can insert many rows in a single INSERT command.

Even if you supply values for a subset of the columns, a complete row must be created. The blank columns will be filled with their default values.

mydb=> CREATE TABLE farm_products (
mydb(>   name   text DEFAULT 'unnamed',
mydb(>   price  numeric(7,2),
mydb(>   units_in_stock  int
mydb(> );
CREATE TABLE
mydb=> INSERT INTO farm_products (name, price, units_in_stock) VALUES
mydb->   ('Carrots', 1.50, 64),
mydb->   ('Garlic', 2.00, 40);
INSERT 0 2
mydb=> SELECT * FROM farm_products;
  name   | price | units_in_stock 
---------+-------+----------------
 Carrots |  1.50 |             64
 Garlic  |  2.00 |             40
(2 rows)

You can explicitly request default values for individual columns (using DEFAULT) or an entire row (using DEFAULT VALUES).

mydb=> INSERT INTO farm_products (name, units_in_stock) VALUES (DEFAULT, 0);
INSERT 0 1
mydb=> INSERT INTO farm_products DEFAULT VALUES;
INSERT 0 1
mydb=> SELECT * FROM farm_products;
  name   | price | units_in_stock 
---------+-------+----------------
 Carrots |  1.50 |             64
 Garlic  |  2.00 |             40
 unnamed |       |              0
 unnamed |       |               
(4 rows)

You can also insert the results of a query:

mydb=> SELECT * FROM products LIMIT 3;
   name   | items_in_stock | price 
----------+----------------+-------
 Apples   |            100 | 25.00
 Bananas  |             32 | 10.00
 Cherries |             74 |  3.00
(3 rows)

mydb=> INSERT INTO farm_products (name, price, units_in_stock)
mydb->   SELECT name, price, items_in_stock FROM products LIMIT 3;
INSERT 0 3
mydb=> SELECT * FROM farm_products;
   name   | price | units_in_stock 
----------+-------+----------------
 Carrots  |  1.50 |             64
 Garlic   |  2.00 |             40
 unnamed  |       |              0
 unnamed  |       |               
 Apples   | 25.00 |            100
 Bananas  | 10.00 |             32
 Cherries |  3.00 |             74
(7 rows)

Tip

When inserting a lot of data at the same time, consider using the more efficient COPY command (or psql’s \copy command).