Adding data, INSERT operator

The operator INSERT is used to add new records to the table.

General query structure with the INSERT operator

MySQL
INSERT INTO table_name [(table_field, ...)]
VALUES (value_of_table_field, ...)
| SELECT table_field, ... FROM table_name ...

Values can be inserted by enumeration using the word VALUES listing them in parentheses separated by commas or using the operator SELECT.

Thus, You can add new entries in the following ways:

  • Using the syntax INSERT INTO ... SELECT

    MySQL
    INSERT INTO Goods (good_id, good_name, type)
    SELECT 20, 'Table', 2;
    
  • Using the syntax INSERT INTO ... VALUES (...)

    MySQL
    INSERT INTO Goods (good_id, good_name, type)
    VALUES (20, 'Table', 2);
    

Each of these queries will give the same result:

good_idgood_nametype
1apartment fee1
2phone fee1
3bread2
4milk2
5red caviar3
6cinema4
7black caviar3
8cough tablets5
9potato2
10pineapples3
11television8
12vacuum cleaner8
13jacket7
14fur coat7
15music school fee6
16english school fee6
20Table2

Primary key when adding a new record

It should be remembered that the primary key of the table is a unique value and adding an existing value will result in an error.

When adding a new record with unique indices, the choice of such a unique values can be a daunting task. The solution may be an additional request, aimed at identifying the maximum value of the primary key to generate a new unique value.

MySQL
INSERT INTO Goods SELECT MAX(good_id) + 1, 'Table', 2 FROM Goods;

Here, we use the MAX function to find the maximum value in the primary key column. However, this method is not the most reliable or universal way to determine the primary key value, as it only works best with numeric data types. For all other data types, implementing this approach would be more complex. Additionally, using this method may result in retrieving a value that was previously present in the table but has since been deleted, leading to potential data inconsistencies in the system 💥. Therefore, it is recommended that an alternative method be used in real-world projects.

MySQL

MySQL introduced a mechanism for its automatic generation. To do this, just provide the primary key good_id attribute AUTO_INCREMENT. Then when creating a new record as the value good_id just pass NULL or 0 - the field will automatically get a value greater than the previous one by one.

MySQL
CREATE TABLE Goods (
	good_id INT NOT NULL AUTO_INCREMENT
	...
);
MySQL
INSERT INTO Goods VALUES (NULL, 'Table', 2);

PostgreSQL

PostgreSQL has a similar mechanism for automatically generating a unique identifier. For this, it has types SMALLSERIAL, SERIAL, BIGSERIAL, which are not real types, but rather just the convenience of writing columns with a unique identifier. A column with one of the above types will be integer and will automatically grow when a new record is added.

MySQL
CREATE TABLE Goods (
	good_id SERIAL
	...
);
MySQL
INSERT INTO Goods (good_name, type) VALUES ('Table', 2);