PostgreSQL - INSERT INTO Table - PostgreSQL - dyclassroom

In this tutorial we will learn to insert data into tables in Postgres.

We are going to use the following tables that we created in the CREATE table tutorial.

employee
comments

Points covered in this tutorial.

INSERT syntax
INSERT by specifying the column names
INSERT multiple rows
Insert into employee table
Insert into comments table with foreign key

INSERT syntax

To insert data into the table we use the following syntax.

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

Where, table_name is the name of the table in which we are going to insert the following value1, value2, ... values.

INSERT by specifying the column names

We use the following to insert data into specific columns.

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

Where, table_name is the name of the table in which we are going to insert the values value1, value2, ... in columns column1, column2, ... respectively.

INSERT multiple rows

To insert multiple rows in a table we use the following syntax.

INSERT INTO table_name(column1, column2, ...)
VALUES
('val1', 'val2', ...),
('val11', 'val22', ...);

Insert into employee table

In this section we are going to insert data into the employeetable.

We are inserting values for the given columns: employeeId, firstName, lastName, score, birthday, email, lastModifiedAt and createdAt.

INSERT INTO employee
("employeeId", "firstName", "lastName", email, birthday, "lastModifiedAt", "createdAt")
VALUES
('e01', 'Yusuf', 'Shakeel', 'yusuf@example.com', '1900-01-01', '2021-01-01 01:01:01', '2021-01-01 01:01:01');

We will see the following output.

INSERT 0 1

On successful completion INSERT returns command tag of the form INSERT oid count.

The count is the number of rows inserted or updated. oid is always 0.

Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.

For more details checkout this INSERT documentation of Postgres.

Note! value for the score column is skipped so, it will get the default value set for it and i.e., 0.

If we want to check the inserted row then we can use the SELECT query.

postgres_project=# SELECT * FROM employee;
 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |       email
------------+-----------+----------+-------+---------------------+---------------------+------------+-------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
(1 row)

We will learn about SELECT query in the later part of this tutorial series.

Lets insert two more employee details and this time let us insert values for all the columns.

INSERT INTO employee
("employeeId", "firstName", "lastName", email, score, birthday, "lastModifiedAt", "createdAt")
VALUES
('e02', 'John', 'Doe', 'johndoe@example.com', 8, '1900-02-03', '2018-01-01 01:01:04', '2018-01-01 01:01:04'),
('e03', 'Jane', 'Doe', 'janedoe@example.com', 6, '1900-05-20', '2018-01-01 01:01:04', '2018-01-01 01:01:04');

We will see the following output.

INSERT 0 2

If we now run the SELECT query we will get three rows in the employee table.

postgres_project=# SELECT * FROM employee;
 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(3 rows)

Insert into comments table with foreign key

The comments table has a foreign key employeeId.

So, when we insert a row in the comments table we also have to provide a value for the employeeId column and this value must exists in the employee table.

In the following example we are inserting a comment from the e03 employeeId account.

INSERT INTO comments
("employeeId", "commentBody", "lastModifiedAt", "createdAt")
VALUES
('e03', 'Awesome', '2021-01-01 02:03:04', '2021-01-01 02:03:04');

If we run the SELECT query we will get the entry we made.

postgres_project=# SELECT * FROM comments;
 commentId | commentBody |   lastModifiedAt    |      createdAt      | employeeId
-----------+-------------+---------------------+---------------------+------------
         3 | Awesome     | 2021-01-01 02:03:04 | 2021-01-01 02:03:04 | e03
(1 row)

If we try to insert a value in the employeeId column of the comments table that does not exists in the employee table then we will get an error.

The following insert query will throw error.

INSERT INTO comments
("employeeId", "commentBody", "lastModifiedAt", "createdAt")
VALUES
('01', 'Awesome', '2021-01-01 02:03:04', '2021-01-01 02:03:04');

Response:

ERROR:  insert or update on table "comments" violates foreign key constraint "employeeId_comments_fk"
DETAIL:  Key (employeeId)=(01) is not present in table "employee".