PostgreSQL Insert Statement
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.
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
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 postgrescommand to install PostgreSQL. After the installation is complete, use the
postgres -D /usr/local/var/postgrescommand to start the process as a background daemon; alternatively, you can use
pg_ctl -D /usr/local/var/postgres startto 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
sudo su - postgres
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:
CREATE DATABASE test;
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:
CREATE TABLE demo_tbl(
id INT NOT NULL PRIMARY KEY,
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:
Postgres ‘INSERT INTO’ statement
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
The basic syntax for the
INSERT statement is:
INSERT INTO demo_tbl (column1, column2) VALUES (value1, value2);
In this example, we use the
INSERT statement to add a single record to the
INSERT INTO demo_tbl(id, string, message)
VALUES (1, 'Greetings', 'Hello World!');
To verify that our
INSERT statement executed correctly, we can use a
SELECT * FROM demo_tbl;
The above query should return the following results:
id | string | message
1 | Greetings | Hello World!
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
INSERT INTO demo_tbl (id, string, message)
VALUES (2, 'Celebrate', 'Happy Birthday!'), (3, 'Insert', 'Statement');
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
test=# SELECT * FROM demo_tbl;
id | string | message
1 | Greetings | Hello World!
2 | Celebrate | Happy Birthday!
3 | INSERT | Statement
Once you’re finished, just use the
\q command to exit the
psql client interface for PostgreSQL.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started