INSERT INTO: Add New Data

The INSERT INTO statement adds new rows to a table.
You can give values for all the columns, or only for some of them.


Syntax (Insert All Columns)

INSERT INTO table_name VALUES (value1, value2, ...);

Syntax (Insert Specific Columns)

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example 1: Insert a New Employee (All Columns)

INSERT INTO employees
VALUES (21, 'Emily', 29, 'IT', 'female');

Output:

INSERT 0 1

This adds a new row to the employees table. psql answers with INSERT 0 1. The 1 is the number of rows that were added. You can ignore the 0. It is an old value that PostgreSQL still prints.

Example 2: Insert a New Employee (Specific Columns)

INSERT INTO employees (id, name, age)
VALUES (22, 'Alex', 27);

Output:

INSERT 0 1

This adds a new employee with only an id, a name and an age. The columns you leave out get NULL, which means "no value".

Example 3: Insert Several Rows at Once

INSERT INTO employees VALUES
(23, 'Maya', 31, 'Sales', 'female'),
(24, 'Sam', 26, 'HR', 'male');

Output:

INSERT 0 2

Put a comma between the rows. INSERT 0 2 means two rows were added.

Now look at the new rows:

SELECT * FROM employees WHERE id > 20;

Output:

 id | name  | age | department | gender 
----+-------+-----+------------+--------
 21 | Emily |  29 | IT         | female
 22 | Alex  |  27 |            | 
 23 | Maya  |  31 | Sales      | female
 24 | Sam   |  26 | HR         | male
(4 rows)

Alex has an empty department and gender. psql shows NULL as an empty space.


Tip

  • The values must be in the same order as the columns.
  • If you don't list the columns, you must give a value for every column, in the order the table has them.

In the exercise, add a new employee to the table.