# How to insert and delete data in PostgreSQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. The `INSERT` and `DELETE` commands are the primary way that PostgreSQL adds and removes records from tables. This guide demonstrates how to use them to control the data your tables manage. Canonical URL: https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/inserting-and-deleting-data Authors: Justin Ellingwood Content revision: 91b5ea3b95a0824bcd0e0d61f68d6121c6e45039fde2b5f874c4c65eec29030e Source revision: b6716699f6439c3ba6ae3bf7116b7da84963c978 ## Introduction Adding and removing records from tables are some of the most common operations that databases perform. Adding data involves specifying the [table](https://www.prisma.io/dataguide/intro/database-glossary#table) and [column](https://www.prisma.io/dataguide/intro/database-glossary#column) names you wish to add values to as well as the values you wish to enter into each fields. Deleting records involves identifying the correct row or rows and removing them from the table. In this guide, we will cover how to use the SQL `INSERT` and `DELETE` commands with PostgreSQL. This includes the basic syntax, how to return data information about the data that was processed, and how to add or remove multiple rows in a single statement. ## Reviewing the table's structure Before using the `INSERT` command, you must know the table's structure so that you can accommodate the requirements imposed by the table's columns, [data types](https://www.prisma.io/dataguide/intro/database-glossary#data-type), and [constraints](https://www.prisma.io/dataguide/intro/database-glossary#constraint). There are a few different ways of doing this depending on your database client. If you are using the `psql` command line client, the most straightforward way to find this information is to use the `\d+` meta command built into the tool. For instance, to find the structure of a table called `employee`, you would type this: ```sql no-lines \d+ employee ``` ``` Table "public.employee" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+-----------------------------+-----------+----------+-----------------------------------------------+----------+--------------+------------- employee_id | integer | | not null | nextval('employee_employee_id_seq'::regclass) | plain | | first_name | character varying(45) | | not null | | extended | | last_name | character varying(45) | | not null | | extended | | last_update | timestamp without time zone | | not null | now() | plain | | Indexes: "employee_pkey" PRIMARY KEY, btree (employee_id) "idx_employee_last_name" btree (last_name) Triggers: last_updated BEFORE UPDATE ON employee FOR EACH ROW EXECUTE FUNCTION last_updated() Access method: heap ``` The output displays the table's column names, data types, and default values, among others. The `\d+` meta command is only available with the `psql` client, so if you are using a different client, you might have to query the table information directly. You can get most of the relevant information with a query like this: ```sql SELECT column_name, data_type, column_default, is_nullable, character_maximum_length FROM information_schema.columns WHERE table_name ='employee'; ``` ``` column_name | data_type | column_default | is_nullable | character_maximum_length -------------+-----------------------------+-----------------------------------------------+-------------+-------------------------- employee_id | integer | nextval('employee_employee_id_seq'::regclass) | NO | first_name | character varying | | NO | 45 last_name | character varying | | NO | 45 last_update | timestamp without time zone | now() | NO | (4 rows) ``` These should give you a good idea of the table's structure so that you can insert values correctly. ## Using `INSERT` to add new records to tables The SQL `INSERT` command is used to add rows of data to an existing table. Once you know the table's structure, you can construct a command that matches the table's columns with the corresponding values you wish to insert for the new record. The basic syntax of the command looks like this: ```sql INSERT INTO my_table(column1, column2) VALUES ('value1', 'value2'); ``` The columns in the column list correspond directly to the values provided within the value list. By default, the `INSERT` command returns the object ID (usually 0) and a count of rows that were successfully inserted: ``` INSERT 0 1 ``` As an example, to insert a new employee into the `employee` table listed above, we could type: ```sql INSERT INTO employee(first_name, last_name) VALUES ('Bob', 'Smith'); ``` ``` INSERT 0 1 ``` Here, we provide values for the `first_name` and `last_name` columns while leaving the other columns to be populated by their default values. If you query the table, you can see that the new record has been added: ```sql SELECT * FROM employee; ``` ``` employee_id | first_name | last_name | last_update -------------+------------+-----------+---------------------------- 1 | Bob | Smith | 2020-08-19 21:07:00.952454 (1 row) ``` ## Returning data from `INSERT` statements If you want additional information about the data that was added to the table, you can include the `RETURNING` clause at the end of your statement. The `RETURNING` clause specifies the columns to display of the records that were just inserted. For instance, to display all of the columns for the records that were just inserted, you could type something like this: ```sql INSERT INTO my_table(column_name, column_name_2) VALUES ('value', 'value2') RETURNING *; ``` ``` column_name | column_name_2 -------------+--------------- value | value2 (1 row) INSERT 0 1 ``` Using the `employee` table, this would look something like this: ```sql INSERT INTO employee(first_name, last_name) VALUES ('Sue', 'Berns') RETURNING *; ``` ``` employee_id | first_name | last_name | last_update -------------+------------+-----------+-------------------------- 2 | Sue | Berns | 2020-08-19 21:15:01.7622 (1 row) INSERT 0 1 ``` You can also choose to return only specific columns from insertions. For instance, here, we only are interested in the new employee's ID: ```sql INSERT INTO employee(first_name, last_name) VALUES ('Delores', 'Muniz') RETURNING employee_id; ``` ``` employee_id ------------- 3 (1 row) INSERT 0 1 ``` As usual, you can also use column aliases to change the column names in the output: ```sql INSERT INTO employee(first_name, last_name) VALUES ('Simone', 'Kohler') RETURNING employee_id AS "Employee ID"; ``` ``` Employee ID ------------- 4 (1 row) INSERT 0 1 ``` ## Using `INSERT` to add multiple rows at once Inserting records one statement at a time is more time consuming and less efficient than inserting multiple rows at once. PostgreSQL allows you to specify multiple rows to add to the same table. Each new row is encapsulated in parentheses, with each set of parentheses separated by commas. The basic syntax for multi-record insertion looks like this: ```sql INSERT INTO my_table(column_name, column_name_2) VALUES ('value', 'value2'), ('value3', 'value4'), ('value5', 'value6'); ``` For the `employee` table we've been referencing, you could add four new employees in a single statement by typing: ```sql INSERT INTO employee(first_name, last_name) VALUES ('Abigail', 'Spencer'), ('Tamal', 'Wayne'), ('Katie', 'Singh'), ('Felipe', 'Espinosa'); ``` ``` INSERT 0 4 ``` ## Using `DELETE` to remove rows from tables The SQL `DELETE` command is used to remove rows from tables, functioning as the complementary action to `INSERT`. In order to remove rows from a table, you must identify the rows you wish to target by providing match criteria within a `WHERE` clause. The basic syntax looks like this: ```sql DELETE FROM my_table WHERE ; ``` For instance, to every row in our `employee` table that has its `first_name` set to `Abigail`, we could type this: ```sql DELETE FROM employee WHERE first_name = 'Abigail'; ``` ``` DELETE 1 ``` The return value here indicates that the `DELETE` command was processed with a single row being removed. ## Returning data from `DELETE` statements As with the `INSERT` command, you can return the affected rows or specific columns from the deleted rows by adding a `RETURNING` clause: ```sql DELETE FROM my_table WHERE RETURNING *; ``` For instance, we can verify that the correct record is removed by returning all of the columns from the deleted `employee` here: ```sql DELETE FROM employee WHERE last_name = 'Smith' RETURNING *; ``` ``` employee_id | first_name | last_name | last_update -------------+------------+-----------+---------------------------- 1 | Bob | Smith | 2020-08-19 21:07:00.952454 (1 row) DELETE 1 ``` ## Using `DELETE` to remove multiple rows at once You can remove multiple items at once with `DELETE` by manipulating the selection criteria specified in the `WHERE` clause. For instance, to remove multiple rows by ID, you could type something like this: ```sql DELETE FROM employee WHERE employee_id in (3,4) RETURNING *; ``` ``` employee_id | first_name | last_name | last_update -------------+------------+-----------+---------------------------- 3 | Delores | Muniz | 2020-08-19 21:17:06.943608 4 | Simone | Kohler | 2020-08-19 21:19:19.298833 (2 rows) DELETE 2 ``` You can even leave out the `WHERE` clause to remove all of the rows from a given table: ```sql DELETE FROM employee RETURNING *; ``` ``` employee_id | first_name | last_name | last_update -------------+------------+-----------+---------------------------- 2 | Sue | Berns | 2020-08-19 21:15:01.7622 6 | Tamal | Wayne | 2020-08-19 22:11:53.408531 7 | Katie | Singh | 2020-08-19 22:11:53.408531 8 | Filipe | Espinosa | 2020-08-19 22:11:53.408531 (4 rows) DELETE 4 ``` Be aware, however, that using `DELETE` to empty a table of data is [not as efficient as the `TRUNCATE` command](https://www.postgresql.org/docs/current/sql-truncate.html), which can remove data without scanning the table. ## Conclusion In this article, we introduced some of the most important commands to control what data is in your PostgreSQL tables. The `INSERT` command can be used to add new data to tables, while the `DELETE` command specifies which rows should be removed. Both commands are able to return the rows they affect and can operate on multiple rows at once. These two commands are the primary mechanisms used to manage increase or decrease the number of records your table contains. Getting a handle on their basic syntax as well as the ways that they can be combined with other clauses will allow you to populate and clean your tables as necessary. ## FAQ **How do you perform a batch insert in PostgreSQL?** The basic syntax for multi-record insertion looks like this: ```sql INSERT INTO my_table(column_name, column_name_2) VALUES ('value', 'value2'), ('value3', 'value4'), ('value5', 'value6'); ``` An example using employee data would look something like this: ```sql INSERT INTO employee(first_name, last_name) VALUES ('Abigail', 'Spencer'), ('Tamal', 'Wayne'), ('Katie', 'Singh'), ('Felipe', 'Espinosa'); ``` **How do you check if a record exists before inserting in PostgreSQL?** One way to check if a record exists in PostgreSQL before inserting is by using the [`EXISTS` subquery expression](https://www.postgresql.org/docs/8.1/functions-subquery.html). The `EXISTS` condition is used in combination with a subquery for the data you are checking for. It is considered to be met if the subquery returns at least one row. If no row is returned, then the record does not yet exist. The basic syntax looks as follows: ```sql WHERE EXISTS ( subquery ); ``` **How do I delete duplicate rows in PostgreSQL?** There are [several methods for deleting duplicate rows](https://www.postgresqltutorial.com/postgresql-tutorial/how-to-delete-duplicate-rows-in-postgresql/) in PostgreSQL. You can use a `DELETE USING` statement to check if two different rows have the same value and then delete the duplicate. In addition, you can use a subquery to delete duplicates or by using an immediate table with the listed steps: 1. Create a new table with the same structure as the one whose duplicate rows should be removed. 2. Insert distinct rows from the source table to the immediate table. 3. Drop the source table. 4. Rename the immediate table to the name of the source table. **How do I delete a record if it exists in PostgreSQL?** You can delete a record in PostgreSQL if it exists by using a `DELETE` statement with a `WHERE` clause including `EXISTS` . The `EXISTS` clause requires a subquery. The basic syntax looks something like this: ```sql DELETE FROM table_name WHERE EXISTS ( subquery ); ``` **How do I delete records with a limit in PostgreSQL?** PostgreSQL only allows for a `LIMIT` clause in its `SELECT` statements. Therefore, in order to use it in a `DELETE` statement you will have to include a `SELECT`. The syntax could look something like this: ```sql DELETE FROM table_name WHERE field_name IN ( SELECT field_name FROM table_name LIMIT 1); ```