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 commandsystemctl status postgresql
. You can also check the version ofpsql
, the interactive terminal for PostgreSQL, by using the commandpsql -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 thebrew install postgres
command to install PostgreSQL. After the installation is complete, use thepostgres -D /usr/local/var/postgres
command to start the process as a background daemon; alternatively, you can usepg_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:
1 | sudo su - postgres |
After entering the password for postgres
, use the following command:
1 | psql |
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:
1 | 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:
1 2 3 4 5 | CREATE TABLE demo_tbl( id INT NOT NULL PRIMARY KEY, string TEXT, message VARCHAR(20) ); |
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:
1 | INSERT INTO demo_tbl (column1, column2) VALUES (value1, value2); |
In this example, we use the INSERT
statement to add a single record to the demo_tbl
table:
1 2 | 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
:
1 | SELECT * FROM demo_tbl; |
The above query should return the following results:
1 2 3 4 | id | string | message ----+-----------+-------------- 1 | Greetings | Hello World! (1 ROW) |
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:
1 2 | 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 demo_tbl
table:
1 2 3 4 5 6 7 | test=# SELECT * FROM demo_tbl; id | string | message ----+-----------+----------------- 1 | Greetings | Hello World! 2 | Celebrate | Happy Birthday! 3 | INSERT | Statement (3 ROWS) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started