PostgreSQL Insert Statement

Introduction

If you’re using PostgreSQL to store data, you’ll need to know how to insert data into tables. Fortunately, the PostgreSQL INSERT statement syntax is easy to master, allowing you to insert either a single record or multiple records at once. In this article, we’ll take a closer look at the PostgreSQL INSERT statement and review some examples of its use.

Prerequisites

Before you begin this tutorial, be sure the following prerequisites are in place:

• You should have some basic knowledge of SQL commands and database management.

• You’ll need to have PostgreSQL installed and running on your machine. If you’re running a Linux distribution that uses systemd, you can check to see if PostgreSQL is installed and working properly by using the command systemctl status postgresql. You can also check the version of psql, the interactive terminal for PostgreSQL, by using the command psql -V

• If you’re running macOS, you can install Postgres using Homebrew. First, you’ll need to update and check the health of your Homebrew installation with the following: brew doctor && brew update. Once these commands are finished, use the brew install postgres command to install PostgreSQL. After the installation is complete, use the postgres -D /usr/local/var/postgres command to start the process as a background daemon; alternatively, you can use pg_ctl -D /usr/local/var/postgres start to run the server in the foreground of a terminal window.

PostgreSQL INSERT statement

Now that we’ve reviewed the prerequisites, let’s access psql so that we can create a sample database and test out some INSERT INTO SQL statements.

Use psql to connect to a database

Before we can try some INSERT statement examples, we’ll need to connect to PostgreSQL using the interactive terminal psql. We’ll be using this command-line interface to create a database and build any necessary tables and to perform any SQL commands.

To access the psql console, we’ll need the privileges of the postgres superuser:

After entering the password for postgres, use the following command:

Postgres ‘CREATE DATABASE’ statement

Next, let’s set up a sample database and table that we’ll use in our examples. The basic syntax for creating a database is:

Note: You can use the command \c followed by the database name test to connect to that database. Unlike SQL statements, commands that begin with a backslash (\) in psql do not have to be terminated with a semicolon.

Postgres ‘CREATE TABLE’ statement

Once we’re connected to the database, we can create a table.

Here’s the command we’ll use to create a table in PostgreSQL:

Keep in mind that the constraints we included are optional– you can set up your table however you prefer.

When you create a table in PostgreSQL, you need to specify the data type of each column. In the example shown above, we used three different data types: INT, TEXT and VARCHAR.

Postgres ‘INSERT INTO’ statement

The PostgreSQL INSERT statement is used to insert a new single record or multiple records into a specified table. In this section, we’ll populate our newly-created table with some records using the INSERT statement.

The basic syntax for the INSERT statement is:

In this example, we use the INSERT statement to add a single record to the demo_tbl table:

To verify that our INSERT statement executed correctly, we can use a SELECT:

The above query should return the following results:

Using SQL and ‘INSERT INTO’ for multiple rows

If you need to insert multiple records into a PostgreSQL table, you can use the same INSERT INTO SQL statement with a slightly different syntax.

The following example shows how to insert multiple records into the demo_tbl table:

Notice how each record is contained within parentheses, and each column value within the record is delimited by commas.

The result of the INSERT statement shown above should return a response of INSERT 0 2.

Now, let’s use the SELECT * FROM demo_tbl; statement to have psql return results like the ones shown below. This will allow us to see how many records were inserted into the demo_tbl table:

Once you’re finished, just use the \q command to exit the psql client interface for PostgreSQL.

Conclusion

If you’re just getting started with PostgreSQL, it’s important to learn how to insert records correctly. In this article, we showed you how to use the PostgreSQL INSERT statement to insert both a single record as well as multiple records. With our examples to guide you, you’ll be ready to insert records into your own PostgreSQL tables.