PostgreSQL - INSERT INTO Table
PostgreSQL
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.
employeecomments
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".
ADVERTISEMENT