How to insert and delete data in PostgreSQL

Introduction

Adding and removing records from tables are some of the most common operations that databases perform. Adding data involves specifying the table and 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, and constraints. 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:

\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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

DELETE FROM my_table
WHERE <condition>;

For instance, to every row in our employee table that has its first_name set to Abigail, we could type this:

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:

DELETE FROM my_table
WHERE <condition>
RETURNING *;

For instance, we can verify that the correct record is removed by returning all of the columns from the deleted employee here:

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:

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:

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, 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

INSERT INTO my_table(column_name, column_name_2)
VALUES
    ('value', 'value2'),
    ('value3', 'value4'),
    ('value5', 'value6');

INSERT INTO employee(first_name, last_name)
VALUES
    ('Abigail', 'Spencer'),
    ('Tamal', 'Wayne'),
    ('Katie', 'Singh'),
    ('Felipe', 'Espinosa');

WHERE EXISTS ( subquery );

DELETE FROM table_name
    WHERE EXISTS ( subquery );

DELETE FROM table_name
WHERE field_name IN (
    SELECT field_name FROM table_name LIMIT 1);