PostgreSQL INSERT: Inserting Data into a Table

Summary: in this tutorial, you will learn how to use the PostgreSQL INSERT statement to insert one or more rows into a table.

Introduction to PostgreSQL INSERT statement

After creating a table, you can insert one or more rows into it. You use the PostgreSQL INSERT statement to insert a new row into a table.

Here’s the basic syntax of the INSERT statement:

INSERT INTO table_name(column1, column2, ...)
VALUES(value1, value2, ...);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

INSERT INTO are the keywords instructing PostgreSQL to insert a new row into a table.
table_name is the name of the table you want to add data.
After the table name, specify a comma-separated list of columns within the parentheses ().
VALUES is a keyword telling PostgreSQL about the data you want to add to the table.
After the VALUES keyword, specify a comma-separated list of values corresponding to the columns within a set of parentheses.

The semicolon (;) at the end of the statement is optional. It can be helpful when you have two statements and want to separate them.

Inserting one row into a table example

Suppose you have a table called inventories:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example uses the INSERT statement to insert a new row into the inventories table:

INSERT INTO inventories (name, brand, quantity, price)
VALUES ('iPhone 14 Pro', 'Apple', 10, 999.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that the order of columns listed after the table name does not matter. You can change the column order as you like. For example:

INSERT INTO inventories (brand, name, quantity, price)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

But you also need to change the order of values so that they match the columns:

VALUES ('Apple', 'iPhone 14 Pro', 10, 999.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we swap the order of the name and brand columns and the order of 'iPhone 14 Pro' and 'Apple'.

Inserting multiple rows into a table example

PostgreSQL allows you to insert multiple rows into a table at once using the following syntax:

INSERT INTO table_name(column1, column2, ...)
VALUES
     (value11, value12, ...),
     (value21, value22, ...),
     (value31, value32, ..),
     ...;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, instead of specifying one row of data in the VALUES clause, you provide multiple rows of data.

For example, the following statement inserts two rows into the inventories table:

INSERT INTO
  inventories (name, brand, quantity, price)
VALUES
  ('Galaxy S23 Ultra', 'Samsung', 15, 1199.99),
  ('Pixel 7 Pro', 'Google', 8, 899.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Returning the inserted row

To return the inserted row, you add the RETURNING clause to the INSERT statement:

INSERT INTO table_name(column1, column2, ...)
VALUES(value1, value2, ...)
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The asterisk (*) means returning all columns of the inserted row. To select columns to return, you can specify the column names in the RETURNING clause:

INSERT INTO table_name(column1, column2, ...)
VALUES(value1, value2, ...)
RETURNING column1, column2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we return the column1 and column2 of the inserted row.

The following example inserts a new row into the inventories table and returns the inserted row:

INSERT INTO inventories (name, brand, quantity, price)
VALUES ('Xperia 1 IV', 'Sony', 7, 1299.99)
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

    name     | brand | quantity |  price
-------------+-------+----------+---------
 Xperia 1 IV | Sony  |        7 | 1299.99Code language: plaintext (plaintext)

Summary

Use the INSERT statement to insert one or more rows into a table.
Use the RETURNING clause to return inserted rows.