PostgreSQL: INSERT Statement

In this PostgreSQL post explains how to use the PostgreSQL INSERT statement with syntax and examples.

Description

The PostgreSQL INSERT statement is used to insert a single record or multiple records into a table in PostgreSQL.

Syntax

The syntax for the PostgreSQL INSERT statement when inserting record(s) using the VALUES keyword is:

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1 | DEFAULT, expression2 | DEFAULT, ... ),
(expression1 | DEFAULT, expression2 | DEFAULT, ... ),
...;

Or...

The syntax for the INSERT statement when inserting a single record using the DEFAULT VALUES keyword in PostgreSQL is:

INSERT INTO table
(column1, column2, ... )
DEFAULT VALUES;

Or...

The syntax for the INSERT statement when inserting multiple records using a sub-select in PostgreSQL is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

Parameters or Arguments

table

The table to insert the records into.

column1, column2

The columns in the table to insert values.

expression1 | DEFAULT, expression2 | DEFAULT

Used in the first syntax. These are the values to assign to the columns in the table. If expression1 is specified, then column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on. If DEFAULT is specified, the corresponding column will be populated with its default value.

DEFAULT VALUES

Used in the second syntax. All columns will be populated with their default values.

source_table

Used in the third syntax. It is the source table when inserting data from another table.

WHERE conditions

Optional. Used in the third syntax. They are the conditions that must be met for the records to be inserted.

Note

• When inserting records into a table using the PostgreSQL INSERT statement, you must provide a value for every NOT NULL column.
• You can omit a column from the PostgreSQL INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

The simplest way to create a PostgreSQL INSERT query to list the values using the VALUES keyword.

For example:

INSERT INTO contacts
(contact_id, last_name, first_name, country)
VALUES
(250, 'Mark', 'Jane', DEFAULT);

This PostgreSQL INSERT statement would result in one record being inserted into the contacts table. This new record would have a contact_id of 250, a last_name of 'Mark', first_name of 'Jane', and whatever the default value is for the country field.

You could use the syntax above to insert more than one record at a time.

For example:

INSERT INTO contacts
(contact_id, last_name, first_name, country)
VALUES
(250, 'Mark', 'Jane', DEFAULT),
(251, 'Smith', 'John', 'US');

This PostgreSQL INSERT statement would result in two records being inserted into the contacts table. The first record would have a contact_id of 250, a last_name of 'Mark', first_name of 'Jane', and whatever the default value is for the country field. The second record would have a contact_id of 251, a last_name of 'Smith', a first_name of 'John' and a country of 'US'.

This would be equivalent to the following two INSERT statements:

INSERT INTO contacts
(contact_id, last_name, first_name, country)
VALUES
(250, 'Mark', 'Jane', DEFAULT);

INSERT INTO contacts
(contact_id, last_name, first_name, country)
VALUES
(251, 'Smith', 'John', 'US');

Example - Using DEFAULT VALUES keyword

In PostgreSQL, you can also insert a record into a table using the DEFAULT VALUES syntax.

For example:

INSERT INTO contacts
(contact_id, last_name, first_name, country)
DEFAULT VALUES;

This PostgreSQL INSERT statement would result in one record being inserted into the contacts table. This new record would be created with default values for the contact_id, last_name, first_name, and country fields.

Example - Using sub-select

You can also create more complicated PostgreSQL INSERT statements using sub-selects.

For example:

INSERT INTO contacts
(last_name, first_name)
SELECT last_name, first_name
FROM customers
WHERE customer_id > 4000;

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following PostgreSQL SELECT statement before performing the insert.

SELECT count(*)
FROM customers
WHERE customer_id > 4000;