Insert Into Statement In PostgreSQL

Faruk Erdem

2 years ago

In today’s article, we will be talking about Adding a Record to a PostgreSQL Table with the Insert Statement.

In order to add data to the table, we need to use the INSERT clause, and new data is added to a separate line according to the values you enter as a result of each insertion.

The usage of INSERT is as follows.

INSERT INTO Table_Name(Column1,Column2) VALUES (value to be entered in column1, value to be entered in column2)

1	INSERT INTO Table_Name(Column1,Column2) VALUES (value to be entered in column1, value to be entered in column2)

The general usage of the INSERT clause above is given. Things to consider in the INSERT clause are listed as follows.

After the INSERT INTO sentence, the name of the table to be added is written.

After typing the Table_Name, which columns will be added to the columns are written with a comma between the columns.

The order of the column names is not important between the parentheses that appear after the table name.

If all columns of the table are to be inserted, if nothing is written after the INSERT INTO clause, all columns are assumed to be written.

After the VALUES section, you need to load the data according to which columns of the table you wrote in which order.

If the table was created as an identity column while creating the table, this column and its value should not be written in parentheses.

When entering data in columns with data types such as text and date, it should be written in quotation marks.

Numeric columns, that is, columns with data types such as integer, decimal are not written in quotes.

It is not allowed to enter the same data in primary key or unique columns.

In the INSERT clause, if a colon is not specified after the table name, it is assumed that the columns are written from left to right.

If the column has a default value in the INSERT clause, the value specified as DEFAULT is added even if the column and data value are not written in the data INSERT clause, or you can print the default value by typing the column name and default in the value part of the column in the values section so that the column with the default value in the VALUES section gets the default value.

As a result of the records made with INSERT, the number of rows inserted is displayed on the screen, but if you want to see the inserted data, it will be sufficient to enter the RETURNING parameter.

Let’s make examples. First, let’s create our table named staff.

CREATE TABLE personnel(id integer,name text, last name text, department text,work_start_date date);

1	CREATE TABLE personnel(id integer,name text, last name text, department text,work_start_date date);

We have created our personnel table above, now let’s load data into the table.

As I mentioned above, we write the name of the table after the INSERT INTO and open the parenthesis and write the columns into which the data will be loaded, Since I want to add data to all of them, I wrote them all.

The next step will be to write the values that you want to add in the values section according to their order.

INSERT INTO personnel (id,name,surname,department,is_start_date)VALUES (1,'Faruk','ERDEM','Database','2012-09-09')

1	INSERT INTO personnel (id,name,surname,department,is_start_date)VALUES (1,'Faruk','ERDEM','Database','2012-09-09')

We wrote the above example because all the data of the tables will be inserted, but we can also do it with the following usage.

INSERT INTO personnel VALUES (1,'Faruk','ERDEM','Database','2012-09-09')

1	INSERT INTO personnel VALUES (1,'Faruk','ERDEM','Database','2012-09-09')

If we want to insert only the name and surname column, it will be sufficient to use it as follows.

INSERT INTO personnel(name,surname) VALUES('Utku','ERDEM');

1	INSERT INTO personnel(name,surname) VALUES('Utku','ERDEM');