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.